/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package jprojetocloseup;

import com.jtattoo.plaf.fast.FastLookAndFeel;
//import java.awt.Font;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.InputStreamReader;
import javax.swing.DefaultComboBoxModel;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import javax.swing.UIManager;
import javax.swing.UnsupportedLookAndFeelException;
import javax.swing.filechooser.FileNameExtensionFilter;
import javax.swing.table.DefaultTableModel;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import java.util.Properties;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.FontFactory;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;

/**
 *
 * @author Rodrigo Toma
 */
public class PrincipalCloseUp extends javax.swing.JFrame {

    File[] arquivoMedico = null;
    File[] arquivoEnvioCloseUp = null;
    String caminhoPlaninhaDefaut = null;
    String caminhoRetornoDefaut = null;
    String caminhoEnvioDefaut = null;
    String caminhoArquivo = null;
    public static Connection con;
    String datainiok, datafimok;

    public PrincipalCloseUp() {
        initComponents();
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jPanel3 = new javax.swing.JPanel();
        jTabbedPane1 = new javax.swing.JTabbedPane();
        jPanel4 = new javax.swing.JPanel();
        jTabbedPane2 = new javax.swing.JTabbedPane();
        jPanel16 = new javax.swing.JPanel();
        jScrollPane5 = new javax.swing.JScrollPane();
        jTableInformacoesResumidas = new javax.swing.JTable();
        jTabbedPane5 = new javax.swing.JTabbedPane();
        jPanel13 = new javax.swing.JPanel();
        jButtonFiltrarEnvios = new javax.swing.JButton();
        jXDatePickerFim = new org.jdesktop.swingx.JXDatePicker();
        jXDatePickerIni = new org.jdesktop.swingx.JXDatePicker();
        jLabel16 = new javax.swing.JLabel();
        jLabel17 = new javax.swing.JLabel();
        jProgressBarInfo = new javax.swing.JProgressBar();
        jTabbedPane6 = new javax.swing.JTabbedPane();
        jPanel11 = new javax.swing.JPanel();
        jTextFieldEnvioCloseUp = new javax.swing.JTextField();
        jButton10 = new javax.swing.JButton();
        jButton11 = new javax.swing.JButton();
        jScrollPane4 = new javax.swing.JScrollPane();
        jTextAreaEnvioCloseUp = new javax.swing.JTextArea();
        jProgressBarQtdeItemEnvio = new javax.swing.JProgressBar();
        jProgressBarLinhasEnvio = new javax.swing.JProgressBar();
        jLabel4 = new javax.swing.JLabel();
        jLabel5 = new javax.swing.JLabel();
        jLabel9 = new javax.swing.JLabel();
        jScrollPane7 = new javax.swing.JScrollPane();
        jTextArea2 = new javax.swing.JTextArea();
        jLabel13 = new javax.swing.JLabel();
        jPanel15 = new javax.swing.JPanel();
        jProgressBarProcRetorno = new javax.swing.JProgressBar();
        jLabel10 = new javax.swing.JLabel();
        jTextFieldCaminhoRetCloseUp = new javax.swing.JTextField();
        jLabelLidas = new javax.swing.JLabel();
        jLabel15 = new javax.swing.JLabel();
        JLabelTotal = new javax.swing.JLabel();
        jButton14 = new javax.swing.JButton();
        jButton13 = new javax.swing.JButton();
        jTabbedPane3 = new javax.swing.JTabbedPane();
        jPanel9 = new javax.swing.JPanel();
        jScrollPane8 = new javax.swing.JScrollPane();
        jTableDLojasDentro = new javax.swing.JTable();
        jPanel10 = new javax.swing.JPanel();
        jScrollPane6 = new javax.swing.JScrollPane();
        jTableDLojasFora = new javax.swing.JTable();
        jPanel12 = new javax.swing.JPanel();
        jScrollPane9 = new javax.swing.JScrollPane();
        jTableDProduto = new javax.swing.JTable();
        jPanel17 = new javax.swing.JPanel();
        jScrollPane10 = new javax.swing.JScrollPane();
        jTableDLaboratorio = new javax.swing.JTable();
        jPanel19 = new javax.swing.JPanel();
        jScrollPane11 = new javax.swing.JScrollPane();
        jTableDMedicos = new javax.swing.JTable();
        jPanel5 = new javax.swing.JPanel();
        jScrollPane1 = new javax.swing.JScrollPane();
        jTableAssociados = new javax.swing.JTable();
        jTabbedPane7 = new javax.swing.JTabbedPane();
        jPanel14 = new javax.swing.JPanel();
        jTextFieldAssociadoViaArq = new javax.swing.JTextField();
        jProgressBarImportAssoc = new javax.swing.JProgressBar();
        jButton3 = new javax.swing.JButton();
        jButton2 = new javax.swing.JButton();
        jTabbedPane8 = new javax.swing.JTabbedPane();
        jPanel18 = new javax.swing.JPanel();
        jTextFieldBusca = new javax.swing.JTextField();
        jButtonBuscar = new javax.swing.JButton();
        jLabel12 = new javax.swing.JLabel();
        jPanel2 = new javax.swing.JPanel();
        jScrollPane2 = new javax.swing.JScrollPane();
        jTableMedicos = new javax.swing.JTable();
        jTabbedPane9 = new javax.swing.JTabbedPane();
        jPanel6 = new javax.swing.JPanel();
        jTextFieldMedico = new javax.swing.JTextField();
        jLabel3 = new javax.swing.JLabel();
        jScrollPane3 = new javax.swing.JScrollPane();
        jTextAreaMedicos = new javax.swing.JTextArea();
        jButton1 = new javax.swing.JButton();
        jButton8 = new javax.swing.JButton();
        jProgressBarQtdeSelecionado = new javax.swing.JProgressBar();
        jProgressBarQtdeLinhas = new javax.swing.JProgressBar();
        jTabbedPane10 = new javax.swing.JTabbedPane();
        jPanel7 = new javax.swing.JPanel();
        jTextFieldBuscaMedico = new javax.swing.JTextField();
        jLabel2 = new javax.swing.JLabel();
        jComboBoxEstado = new javax.swing.JComboBox();
        jButton9 = new javax.swing.JButton();
        jLabel1 = new javax.swing.JLabel();
        jPanel8 = new javax.swing.JPanel();
        jTabbedPane11 = new javax.swing.JTabbedPane();
        jPanel1 = new javax.swing.JPanel();
        jLabel8 = new javax.swing.JLabel();
        jTextFieldCaminhoPlanilha = new javax.swing.JTextField();
        jButton4 = new javax.swing.JButton();
        jButton5 = new javax.swing.JButton();
        jTextFieldCaminhoMedico = new javax.swing.JTextField();
        jLabel6 = new javax.swing.JLabel();
        jLabel7 = new javax.swing.JLabel();
        jTextFieldCaminhoEnvioCloseUp = new javax.swing.JTextField();
        jButton6 = new javax.swing.JButton();
        jButton16 = new javax.swing.JButton();
        jTextFieldCaminhoRetornoCloseUp = new javax.swing.JTextField();
        jLabel11 = new javax.swing.JLabel();
        jButton7 = new javax.swing.JButton();
        jPanel20 = new javax.swing.JPanel();
        jLabel14 = new javax.swing.JLabel();
        jXDatePickerRELini = new org.jdesktop.swingx.JXDatePicker();
        jXDatePickerRELfim = new org.jdesktop.swingx.JXDatePicker();
        jLabel18 = new javax.swing.JLabel();
        jButton12 = new javax.swing.JButton();
        jLabel19 = new javax.swing.JLabel();
        jComboBoxTipoRelatorio = new javax.swing.JComboBox();
        jProgressBarRelatorio = new javax.swing.JProgressBar();

        javax.swing.GroupLayout jPanel3Layout = new javax.swing.GroupLayout(jPanel3);
        jPanel3.setLayout(jPanel3Layout);
        jPanel3Layout.setHorizontalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 100, Short.MAX_VALUE)
        );
        jPanel3Layout.setVerticalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGap(0, 100, Short.MAX_VALUE)
        );

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        setTitle("Projeto Close-UP    Ver.: 1.1");
        addWindowListener(new java.awt.event.WindowAdapter() {
            public void windowActivated(java.awt.event.WindowEvent evt) {
                formWindowActivated(evt);
            }
        });

        jTabbedPane1.addChangeListener(new javax.swing.event.ChangeListener() {
            public void stateChanged(javax.swing.event.ChangeEvent evt) {
                jTabbedPane1StateChanged(evt);
            }
        });

        jTabbedPane2.setFont(new java.awt.Font("Tahoma", 1, 11)); // NOI18N

        jTableInformacoesResumidas.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {},
                {},
                {},
                {}
            },
            new String [] {

            }
        ));
        jScrollPane5.setViewportView(jTableInformacoesResumidas);

        javax.swing.GroupLayout jPanel16Layout = new javax.swing.GroupLayout(jPanel16);
        jPanel16.setLayout(jPanel16Layout);
        jPanel16Layout.setHorizontalGroup(
            jPanel16Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel16Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane5, javax.swing.GroupLayout.PREFERRED_SIZE, 388, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(15, Short.MAX_VALUE))
        );
        jPanel16Layout.setVerticalGroup(
            jPanel16Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel16Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane5, javax.swing.GroupLayout.DEFAULT_SIZE, 572, Short.MAX_VALUE)
                .addContainerGap())
        );

        jTabbedPane2.addTab("Informações", jPanel16);

        jTabbedPane5.setFont(new java.awt.Font("Tahoma", 1, 11)); // NOI18N

        jButtonFiltrarEnvios.setText("Filtrar");
        jButtonFiltrarEnvios.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonFiltrarEnviosActionPerformed(evt);
            }
        });

        jXDatePickerFim.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jXDatePickerFimActionPerformed(evt);
            }
        });

        jXDatePickerIni.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jXDatePickerIniActionPerformed(evt);
            }
        });

        jLabel16.setText("Inicio");

        jLabel17.setText("Fim");

        jProgressBarInfo.setStringPainted(true);

        javax.swing.GroupLayout jPanel13Layout = new javax.swing.GroupLayout(jPanel13);
        jPanel13.setLayout(jPanel13Layout);
        jPanel13Layout.setHorizontalGroup(
            jPanel13Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel13Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel16)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jXDatePickerIni, javax.swing.GroupLayout.PREFERRED_SIZE, 148, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jLabel17)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jXDatePickerFim, javax.swing.GroupLayout.PREFERRED_SIZE, 148, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jButtonFiltrarEnvios)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jProgressBarInfo, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(518, Short.MAX_VALUE))
        );
        jPanel13Layout.setVerticalGroup(
            jPanel13Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel13Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel13Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jXDatePickerIni, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel16)
                    .addComponent(jLabel17)
                    .addComponent(jXDatePickerFim, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButtonFiltrarEnvios, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jProgressBarInfo, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(21, Short.MAX_VALUE))
        );

        jTabbedPane5.addTab("Filtros", jPanel13);

        jTabbedPane6.setFont(new java.awt.Font("Tahoma", 1, 11)); // NOI18N

        jButton10.setIcon(new javax.swing.ImageIcon(getClass().getResource("/jprojetocloseup/lupa16.png"))); // NOI18N
        jButton10.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton10ActionPerformed(evt);
            }
        });

        jButton11.setText("Processar");
        jButton11.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton11ActionPerformed(evt);
            }
        });

        jTextAreaEnvioCloseUp.setColumns(20);
        jTextAreaEnvioCloseUp.setRows(5);
        jScrollPane4.setViewportView(jTextAreaEnvioCloseUp);

        jProgressBarQtdeItemEnvio.setStringPainted(true);

        jProgressBarLinhasEnvio.setStringPainted(true);

        jLabel4.setText("0");

        jLabel5.setText("/");

        jLabel9.setText("0");

        jTextArea2.setColumns(20);
        jTextArea2.setRows(5);
        jScrollPane7.setViewportView(jTextArea2);

        jLabel13.setText("Caminho");

        javax.swing.GroupLayout jPanel11Layout = new javax.swing.GroupLayout(jPanel11);
        jPanel11.setLayout(jPanel11Layout);
        jPanel11Layout.setHorizontalGroup(
            jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel11Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addGroup(jPanel11Layout.createSequentialGroup()
                        .addComponent(jProgressBarQtdeItemEnvio, javax.swing.GroupLayout.PREFERRED_SIZE, 163, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(jProgressBarLinhasEnvio, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                    .addGroup(jPanel11Layout.createSequentialGroup()
                        .addGap(14, 14, 14)
                        .addComponent(jLabel13)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jTextFieldEnvioCloseUp, javax.swing.GroupLayout.PREFERRED_SIZE, 521, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButton10, javax.swing.GroupLayout.PREFERRED_SIZE, 58, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButton11))
                    .addComponent(jScrollPane4))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jLabel4)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel11Layout.createSequentialGroup()
                        .addComponent(jLabel5)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jLabel9))
                    .addComponent(jScrollPane7, javax.swing.GroupLayout.DEFAULT_SIZE, 345, Short.MAX_VALUE))
                .addContainerGap())
        );
        jPanel11Layout.setVerticalGroup(
            jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel11Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addComponent(jButton10, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addComponent(jButton11, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addGroup(jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(jTextFieldEnvioCloseUp, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addComponent(jLabel13)))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane4, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jScrollPane7, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                        .addGroup(jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel4)
                            .addComponent(jLabel5)
                            .addComponent(jLabel9))
                        .addComponent(jProgressBarLinhasEnvio, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addComponent(jProgressBarQtdeItemEnvio, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap())
        );

        jTabbedPane6.addTab("Processar Envio", jPanel11);

        jProgressBarProcRetorno.setStringPainted(true);

        jLabel10.setText("Caminho");

        jTextFieldCaminhoRetCloseUp.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jTextFieldCaminhoRetCloseUpActionPerformed(evt);
            }
        });

        jLabelLidas.setText("0");

        jLabel15.setText("/");

        JLabelTotal.setText("0");

        jButton14.setIcon(new javax.swing.ImageIcon(getClass().getResource("/jprojetocloseup/lupa16.png"))); // NOI18N
        jButton14.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton14ActionPerformed(evt);
            }
        });

        jButton13.setText("Processar");
        jButton13.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton13ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel15Layout = new javax.swing.GroupLayout(jPanel15);
        jPanel15.setLayout(jPanel15Layout);
        jPanel15Layout.setHorizontalGroup(
            jPanel15Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel15Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel15Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel10)
                    .addGroup(jPanel15Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
                        .addGroup(jPanel15Layout.createSequentialGroup()
                            .addComponent(jProgressBarProcRetorno, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                            .addComponent(jLabelLidas)
                            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                            .addComponent(jLabel15)
                            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                            .addComponent(JLabelTotal)
                            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .addComponent(jButton14, javax.swing.GroupLayout.PREFERRED_SIZE, 59, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                            .addComponent(jButton13))
                        .addComponent(jTextFieldCaminhoRetCloseUp, javax.swing.GroupLayout.PREFERRED_SIZE, 684, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap(416, Short.MAX_VALUE))
        );
        jPanel15Layout.setVerticalGroup(
            jPanel15Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel15Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel10)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addComponent(jTextFieldCaminhoRetCloseUp, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel15Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jButton13, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton14)
                    .addGroup(jPanel15Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(jProgressBarProcRetorno, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addComponent(jLabelLidas)
                        .addComponent(jLabel15)
                        .addComponent(JLabelTotal)))
                .addGap(90, 90, 90))
        );

        jTabbedPane6.addTab("Processar Retorno", jPanel15);

        jTableDLojasDentro.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {},
                {},
                {},
                {}
            },
            new String [] {

            }
        ));
        jScrollPane8.setViewportView(jTableDLojasDentro);

        javax.swing.GroupLayout jPanel9Layout = new javax.swing.GroupLayout(jPanel9);
        jPanel9.setLayout(jPanel9Layout);
        jPanel9Layout.setHorizontalGroup(
            jPanel9Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel9Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane8, javax.swing.GroupLayout.DEFAULT_SIZE, 645, Short.MAX_VALUE)
                .addContainerGap(31, Short.MAX_VALUE))
        );
        jPanel9Layout.setVerticalGroup(
            jPanel9Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel9Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane8, javax.swing.GroupLayout.DEFAULT_SIZE, 572, Short.MAX_VALUE)
                .addContainerGap())
        );

        jTabbedPane3.addTab("Lojas que enviaram", jPanel9);

        jTableDLojasFora.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {},
                {},
                {},
                {}
            },
            new String [] {

            }
        ));
        jScrollPane6.setViewportView(jTableDLojasFora);

        javax.swing.GroupLayout jPanel10Layout = new javax.swing.GroupLayout(jPanel10);
        jPanel10.setLayout(jPanel10Layout);
        jPanel10Layout.setHorizontalGroup(
            jPanel10Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel10Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane6, javax.swing.GroupLayout.DEFAULT_SIZE, 666, Short.MAX_VALUE)
                .addContainerGap())
        );
        jPanel10Layout.setVerticalGroup(
            jPanel10Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel10Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane6, javax.swing.GroupLayout.DEFAULT_SIZE, 572, Short.MAX_VALUE)
                .addContainerGap())
        );

        jTabbedPane3.addTab("Lojas que não enviaram", jPanel10);

        jTableDProduto.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {},
                {},
                {},
                {}
            },
            new String [] {

            }
        ));
        jScrollPane9.setViewportView(jTableDProduto);

        javax.swing.GroupLayout jPanel12Layout = new javax.swing.GroupLayout(jPanel12);
        jPanel12.setLayout(jPanel12Layout);
        jPanel12Layout.setHorizontalGroup(
            jPanel12Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel12Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane9, javax.swing.GroupLayout.DEFAULT_SIZE, 666, Short.MAX_VALUE)
                .addContainerGap())
        );
        jPanel12Layout.setVerticalGroup(
            jPanel12Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel12Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane9, javax.swing.GroupLayout.DEFAULT_SIZE, 572, Short.MAX_VALUE)
                .addContainerGap())
        );

        jTabbedPane3.addTab("Produtos", jPanel12);

        jTableDLaboratorio.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {},
                {},
                {},
                {}
            },
            new String [] {

            }
        ));
        jScrollPane10.setViewportView(jTableDLaboratorio);

        javax.swing.GroupLayout jPanel17Layout = new javax.swing.GroupLayout(jPanel17);
        jPanel17.setLayout(jPanel17Layout);
        jPanel17Layout.setHorizontalGroup(
            jPanel17Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel17Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane10, javax.swing.GroupLayout.DEFAULT_SIZE, 666, Short.MAX_VALUE)
                .addContainerGap())
        );
        jPanel17Layout.setVerticalGroup(
            jPanel17Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel17Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane10, javax.swing.GroupLayout.DEFAULT_SIZE, 572, Short.MAX_VALUE)
                .addContainerGap())
        );

        jTabbedPane3.addTab("Laboratórios", jPanel17);

        jTableDMedicos.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {},
                {},
                {},
                {}
            },
            new String [] {

            }
        ));
        jScrollPane11.setViewportView(jTableDMedicos);

        javax.swing.GroupLayout jPanel19Layout = new javax.swing.GroupLayout(jPanel19);
        jPanel19.setLayout(jPanel19Layout);
        jPanel19Layout.setHorizontalGroup(
            jPanel19Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel19Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane11, javax.swing.GroupLayout.DEFAULT_SIZE, 666, Short.MAX_VALUE)
                .addContainerGap())
        );
        jPanel19Layout.setVerticalGroup(
            jPanel19Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel19Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jScrollPane11, javax.swing.GroupLayout.DEFAULT_SIZE, 572, Short.MAX_VALUE)
                .addContainerGap())
        );

        jTabbedPane3.addTab("Médicos", jPanel19);

        javax.swing.GroupLayout jPanel4Layout = new javax.swing.GroupLayout(jPanel4);
        jPanel4.setLayout(jPanel4Layout);
        jPanel4Layout.setHorizontalGroup(
            jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel4Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jTabbedPane6)
                    .addComponent(jTabbedPane5)
                    .addGroup(jPanel4Layout.createSequentialGroup()
                        .addComponent(jTabbedPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 418, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jTabbedPane3)))
                .addContainerGap())
        );
        jPanel4Layout.setVerticalGroup(
            jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel4Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jTabbedPane6, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(15, 15, 15)
                .addComponent(jTabbedPane5, javax.swing.GroupLayout.PREFERRED_SIZE, 83, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addGroup(jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jTabbedPane2)
                    .addComponent(jTabbedPane3))
                .addContainerGap())
        );

        jTabbedPane1.addTab("Principal", jPanel4);

        jTableAssociados.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {},
                {},
                {},
                {}
            },
            new String [] {

            }
        ));
        jScrollPane1.setViewportView(jTableAssociados);

        jTextFieldAssociadoViaArq.setEnabled(false);
        jTextFieldAssociadoViaArq.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jTextFieldAssociadoViaArqActionPerformed(evt);
            }
        });

        jProgressBarImportAssoc.setStringPainted(true);

        jButton3.setIcon(new javax.swing.ImageIcon(getClass().getResource("/jprojetocloseup/lupa16.png"))); // NOI18N
        jButton3.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton3ActionPerformed(evt);
            }
        });

        jButton2.setText("Processar");
        jButton2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton2ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel14Layout = new javax.swing.GroupLayout(jPanel14);
        jPanel14.setLayout(jPanel14Layout);
        jPanel14Layout.setHorizontalGroup(
            jPanel14Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel14Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel14Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel14Layout.createSequentialGroup()
                        .addGroup(jPanel14Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jTextFieldAssociadoViaArq, javax.swing.GroupLayout.PREFERRED_SIZE, 516, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addGroup(jPanel14Layout.createSequentialGroup()
                                .addGap(526, 526, 526)
                                .addComponent(jButton3)))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(jButton2))
                    .addComponent(jProgressBarImportAssoc, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(436, Short.MAX_VALUE))
        );
        jPanel14Layout.setVerticalGroup(
            jPanel14Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel14Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel14Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addGroup(jPanel14Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(jTextFieldAssociadoViaArq)
                        .addComponent(jButton3, javax.swing.GroupLayout.PREFERRED_SIZE, 20, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addComponent(jButton2, javax.swing.GroupLayout.PREFERRED_SIZE, 20, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jProgressBarImportAssoc, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(22, Short.MAX_VALUE))
        );

        jTabbedPane7.addTab("Leitura Via Arquivo", jPanel14);

        jTextFieldBusca.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jTextFieldBuscaActionPerformed(evt);
            }
        });

        jButtonBuscar.setText("Buscar");
        jButtonBuscar.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonBuscarActionPerformed(evt);
            }
        });

        jLabel12.setText("Razão Social / CNPJ");

        javax.swing.GroupLayout jPanel18Layout = new javax.swing.GroupLayout(jPanel18);
        jPanel18.setLayout(jPanel18Layout);
        jPanel18Layout.setHorizontalGroup(
            jPanel18Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel18Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel12)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jTextFieldBusca, javax.swing.GroupLayout.PREFERRED_SIZE, 271, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jButtonBuscar)
                .addContainerGap(659, Short.MAX_VALUE))
        );
        jPanel18Layout.setVerticalGroup(
            jPanel18Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel18Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel18Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel12)
                    .addComponent(jTextFieldBusca, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButtonBuscar))
                .addContainerGap(22, Short.MAX_VALUE))
        );

        jTabbedPane8.addTab("Pesquisa", jPanel18);

        javax.swing.GroupLayout jPanel5Layout = new javax.swing.GroupLayout(jPanel5);
        jPanel5.setLayout(jPanel5Layout);
        jPanel5Layout.setHorizontalGroup(
            jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel5Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane1)
                    .addComponent(jTabbedPane7)
                    .addComponent(jTabbedPane8))
                .addContainerGap())
        );
        jPanel5Layout.setVerticalGroup(
            jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel5Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jTabbedPane7, javax.swing.GroupLayout.PREFERRED_SIZE, 109, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jTabbedPane8, javax.swing.GroupLayout.PREFERRED_SIZE, 84, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 716, Short.MAX_VALUE)
                .addContainerGap())
        );

        jTabbedPane1.addTab("Associados", jPanel5);

        jTableMedicos.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {},
                {},
                {},
                {}
            },
            new String [] {

            }
        ));
        jScrollPane2.setViewportView(jTableMedicos);

        jTextFieldMedico.setEnabled(false);

        jLabel3.setText("Caminho Pasta");

        jScrollPane3.setBorder(null);

        jTextAreaMedicos.setEditable(false);
        jTextAreaMedicos.setColumns(20);
        jTextAreaMedicos.setFont(new java.awt.Font("Miriam", 0, 13)); // NOI18N
        jTextAreaMedicos.setRows(5);
        jTextAreaMedicos.setBorder(javax.swing.BorderFactory.createTitledBorder(""));
        jScrollPane3.setViewportView(jTextAreaMedicos);

        jButton1.setIcon(new javax.swing.ImageIcon(getClass().getResource("/jprojetocloseup/lupa16.png"))); // NOI18N
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        jButton8.setText("Processar");
        jButton8.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton8ActionPerformed(evt);
            }
        });

        jProgressBarQtdeSelecionado.setStringPainted(true);

        jProgressBarQtdeLinhas.setStringPainted(true);

        javax.swing.GroupLayout jPanel6Layout = new javax.swing.GroupLayout(jPanel6);
        jPanel6.setLayout(jPanel6Layout);
        jPanel6Layout.setHorizontalGroup(
            jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel6Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel3)
                    .addGroup(jPanel6Layout.createSequentialGroup()
                        .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                            .addGroup(jPanel6Layout.createSequentialGroup()
                                .addComponent(jTextFieldMedico)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addComponent(jButton1)
                                .addGap(18, 18, 18)
                                .addComponent(jButton8))
                            .addComponent(jScrollPane3, javax.swing.GroupLayout.PREFERRED_SIZE, 808, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addGap(18, 18, 18)
                        .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jProgressBarQtdeSelecionado, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jProgressBarQtdeLinhas, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))))
                .addContainerGap(128, Short.MAX_VALUE))
        );
        jPanel6Layout.setVerticalGroup(
            jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel6Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel3)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jButton1, javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(jButton8, javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(jTextFieldMedico, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel6Layout.createSequentialGroup()
                        .addComponent(jProgressBarQtdeSelecionado, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jProgressBarQtdeLinhas, javax.swing.GroupLayout.PREFERRED_SIZE, 19, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addComponent(jScrollPane3, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(29, Short.MAX_VALUE))
        );

        jTabbedPane9.addTab("Leitura Via Arquivo", jPanel6);

        jLabel2.setText("Estado");

        jComboBoxEstado.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jComboBoxEstadoActionPerformed(evt);
            }
        });

        jButton9.setText("Buscar");
        jButton9.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton9ActionPerformed(evt);
            }
        });

        jLabel1.setText("Nome/CRM");

        javax.swing.GroupLayout jPanel7Layout = new javax.swing.GroupLayout(jPanel7);
        jPanel7.setLayout(jPanel7Layout);
        jPanel7Layout.setHorizontalGroup(
            jPanel7Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel7Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel1)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jTextFieldBuscaMedico, javax.swing.GroupLayout.PREFERRED_SIZE, 365, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jLabel2)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jComboBoxEstado, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jButton9)
                .addContainerGap(526, Short.MAX_VALUE))
        );
        jPanel7Layout.setVerticalGroup(
            jPanel7Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel7Layout.createSequentialGroup()
                .addGap(19, 19, 19)
                .addGroup(jPanel7Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jComboBoxEstado, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel2)
                    .addComponent(jTextFieldBuscaMedico, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel1)
                    .addComponent(jButton9))
                .addContainerGap(19, Short.MAX_VALUE))
        );

        jTabbedPane10.addTab("Pesquisa", jPanel7);

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane2)
                    .addComponent(jTabbedPane9)
                    .addComponent(jTabbedPane10))
                .addContainerGap())
        );
        jPanel2Layout.setVerticalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jTabbedPane9, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jTabbedPane10, javax.swing.GroupLayout.PREFERRED_SIZE, 89, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jScrollPane2, javax.swing.GroupLayout.DEFAULT_SIZE, 622, Short.MAX_VALUE)
                .addContainerGap())
        );

        jTabbedPane1.addTab("Médicos", jPanel2);

        jLabel8.setText("Planilha Associados");

        jButton4.setText("...");
        jButton4.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton4ActionPerformed(evt);
            }
        });

        jButton5.setText("...");
        jButton5.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton5ActionPerformed(evt);
            }
        });

        jLabel6.setText("Arquivo Medicos");

        jLabel7.setText("Arquivo Close-up TXT");

        jButton6.setText("...");
        jButton6.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton6ActionPerformed(evt);
            }
        });

        jButton16.setText("...");
        jButton16.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton16ActionPerformed(evt);
            }
        });

        jLabel11.setText("Arquivo Close-UP XML");

        jButton7.setText("Salvar");
        jButton7.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton7ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel1Layout.createSequentialGroup()
                        .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jLabel6)
                            .addComponent(jLabel8)
                            .addComponent(jLabel7))
                        .addGap(20, 20, 20)
                        .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(jPanel1Layout.createSequentialGroup()
                                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                                    .addComponent(jTextFieldCaminhoMedico)
                                    .addComponent(jTextFieldCaminhoPlanilha, javax.swing.GroupLayout.PREFERRED_SIZE, 465, javax.swing.GroupLayout.PREFERRED_SIZE))
                                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                    .addGroup(jPanel1Layout.createSequentialGroup()
                                        .addGap(18, 18, 18)
                                        .addComponent(jButton5, javax.swing.GroupLayout.PREFERRED_SIZE, 31, javax.swing.GroupLayout.PREFERRED_SIZE))
                                    .addGroup(jPanel1Layout.createSequentialGroup()
                                        .addGap(18, 18, 18)
                                        .addComponent(jButton4, javax.swing.GroupLayout.PREFERRED_SIZE, 31, javax.swing.GroupLayout.PREFERRED_SIZE))))
                            .addGroup(jPanel1Layout.createSequentialGroup()
                                .addComponent(jTextFieldCaminhoEnvioCloseUp, javax.swing.GroupLayout.PREFERRED_SIZE, 465, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addGap(18, 18, 18)
                                .addComponent(jButton6, javax.swing.GroupLayout.PREFERRED_SIZE, 31, javax.swing.GroupLayout.PREFERRED_SIZE))))
                    .addGroup(jPanel1Layout.createSequentialGroup()
                        .addComponent(jLabel11)
                        .addGap(18, 18, 18)
                        .addComponent(jTextFieldCaminhoRetornoCloseUp, javax.swing.GroupLayout.PREFERRED_SIZE, 465, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(18, 18, 18)
                        .addComponent(jButton16, javax.swing.GroupLayout.PREFERRED_SIZE, 31, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addComponent(jButton7))
                .addContainerGap(463, Short.MAX_VALUE))
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel8)
                    .addComponent(jTextFieldCaminhoPlanilha, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton4))
                .addGap(18, 18, 18)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel6)
                    .addComponent(jTextFieldCaminhoMedico, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton5))
                .addGap(18, 18, 18)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jButton6)
                    .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(jTextFieldCaminhoEnvioCloseUp, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addComponent(jLabel7)))
                .addGap(18, 18, 18)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel11)
                    .addComponent(jTextFieldCaminhoRetornoCloseUp, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton16))
                .addGap(18, 18, 18)
                .addComponent(jButton7)
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        jTabbedPane11.addTab("Caminhos", jPanel1);

        javax.swing.GroupLayout jPanel8Layout = new javax.swing.GroupLayout(jPanel8);
        jPanel8.setLayout(jPanel8Layout);
        jPanel8Layout.setHorizontalGroup(
            jPanel8Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel8Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jTabbedPane11)
                .addContainerGap())
        );
        jPanel8Layout.setVerticalGroup(
            jPanel8Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel8Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jTabbedPane11, javax.swing.GroupLayout.PREFERRED_SIZE, 237, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(705, Short.MAX_VALUE))
        );

        jTabbedPane1.addTab("Configurações", jPanel8);

        jLabel14.setText("Início");

        jLabel18.setText("Fim");

        jButton12.setText("Processar");
        jButton12.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton12ActionPerformed(evt);
            }
        });

        jLabel19.setText("Tipo Relatório");

        jComboBoxTipoRelatorio.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Loja", "Laboratório", "Produto", "Médico" }));

        jProgressBarRelatorio.setStringPainted(true);

        javax.swing.GroupLayout jPanel20Layout = new javax.swing.GroupLayout(jPanel20);
        jPanel20.setLayout(jPanel20Layout);
        jPanel20Layout.setHorizontalGroup(
            jPanel20Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel20Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel20Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel19)
                    .addComponent(jComboBoxTipoRelatorio, javax.swing.GroupLayout.PREFERRED_SIZE, 253, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel20Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jXDatePickerRELini, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel14))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel20Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel18)
                    .addGroup(jPanel20Layout.createSequentialGroup()
                        .addComponent(jXDatePickerRELfim, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButton12)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jProgressBarRelatorio, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap(407, Short.MAX_VALUE))
        );
        jPanel20Layout.setVerticalGroup(
            jPanel20Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel20Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel20Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel14)
                    .addComponent(jLabel18)
                    .addComponent(jLabel19))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel20Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jXDatePickerRELini, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jXDatePickerRELfim, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton12)
                    .addComponent(jComboBoxTipoRelatorio, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jProgressBarRelatorio, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap(899, Short.MAX_VALUE))
        );

        jTabbedPane1.addTab("Relatórios", jPanel20);

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jTabbedPane1)
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jTabbedPane1)
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void formWindowActivated(java.awt.event.WindowEvent evt) {//GEN-FIRST:event_formWindowActivated
        jXDatePickerFim.setFormats(new String[]{"dd/MM/yyyy"});
        jXDatePickerIni.setFormats(new String[]{"dd/MM/yyyy"});


    }//GEN-LAST:event_formWindowActivated

    private void jTabbedPane1StateChanged(javax.swing.event.ChangeEvent evt) {//GEN-FIRST:event_jTabbedPane1StateChanged
        if (jTabbedPane1.getSelectedIndex() == 3) {
            LerCaminho lcp = new LerCaminho();
            lcp.RetornoCaminho();

            jTextFieldCaminhoPlanilha.setText(lcp.retcaminhoPlaninha);
            jTextFieldCaminhoMedico.setText(lcp.retcaminhoMedico);
            jTextFieldCaminhoEnvioCloseUp.setText(lcp.retcaminhoEnvioCloseUp);
            jTextFieldCaminhoRetornoCloseUp.setText(lcp.retCaminhoRetornoCloseUp);

        } else if (jTabbedPane1.getSelectedIndex() == 2) {
            try {
                preencheComboEstado();
            } catch (SQLException ex) {
                Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }//GEN-LAST:event_jTabbedPane1StateChanged

    private void jButton16ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton16ActionPerformed
        JFileChooser fc = new JFileChooser();
        fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
        File diretorio = new File("C:\\");

        fc.setCurrentDirectory(diretorio);

        int res = fc.showOpenDialog(null);

        if (res == JFileChooser.APPROVE_OPTION) {

            diretorio = fc.getSelectedFile();
            jTextFieldCaminhoRetornoCloseUp.setText(diretorio.getAbsolutePath());

        } else {
        }
    }//GEN-LAST:event_jButton16ActionPerformed

    private void jButton4ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton4ActionPerformed
        JFileChooser fc = new JFileChooser();
        fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
        File diretorio = new File("C:\\");

        fc.setCurrentDirectory(diretorio);

        int res = fc.showOpenDialog(null);

        if (res == JFileChooser.APPROVE_OPTION) {

            diretorio = fc.getSelectedFile();
            jTextFieldCaminhoPlanilha.setText(diretorio.getAbsolutePath());

        } else {
        }
    }//GEN-LAST:event_jButton4ActionPerformed

    private void jButton5ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton5ActionPerformed
        JFileChooser fc = new JFileChooser();
        fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
        File diretorio = new File("C:\\");

        fc.setCurrentDirectory(diretorio);

        int res = fc.showOpenDialog(null);

        if (res == JFileChooser.APPROVE_OPTION) {

            diretorio = fc.getSelectedFile();
            jTextFieldCaminhoMedico.setText(diretorio.getAbsolutePath());

        } else {
        }
    }//GEN-LAST:event_jButton5ActionPerformed

    private void jButton6ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton6ActionPerformed
        JFileChooser fc = new JFileChooser();
        fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
        File diretorio = new File("C:\\");

        fc.setCurrentDirectory(diretorio);

        int res = fc.showOpenDialog(null);

        if (res == JFileChooser.APPROVE_OPTION) {

            diretorio = fc.getSelectedFile();
            jTextFieldCaminhoEnvioCloseUp.setText(diretorio.getAbsolutePath());

        } else {
        }
    }//GEN-LAST:event_jButton6ActionPerformed

    private void jButton7ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton7ActionPerformed
        LerCaminho lc = new LerCaminho();
        try {
            lc.GravaCaminho(jTextFieldCaminhoPlanilha.getText(), jTextFieldCaminhoMedico.getText(), jTextFieldCaminhoEnvioCloseUp.getText(), jTextFieldCaminhoRetornoCloseUp.getText());
        } catch (FileNotFoundException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }
    }//GEN-LAST:event_jButton7ActionPerformed

    private void jButton9ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton9ActionPerformed
        formWindowOpenedPM(null);
    }//GEN-LAST:event_jButton9ActionPerformed

    private void jComboBoxEstadoActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jComboBoxEstadoActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jComboBoxEstadoActionPerformed

    private void jButton8ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton8ActionPerformed
        formWindowOpened2(null);
    }//GEN-LAST:event_jButton8ActionPerformed

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        LerCaminho lc = new LerCaminho();

        lc.RetornoCaminho();

        JFileChooser fc = new JFileChooser();
        FileNameExtensionFilter filter = new FileNameExtensionFilter("Arquivo de Texto", "txt");
        File diretorio = new File(lc.retcaminhoMedico);
        String linha = "", texto = "";
        int tamanhoLinha = 0;
        boolean proxima = false;

        fc.setMultiSelectionEnabled(true);
        fc.setCurrentDirectory(diretorio);
        fc.addChoosableFileFilter(filter);
        fc.setAcceptAllFileFilterUsed(false);
        fc.setFileFilter(filter);
        int res = fc.showOpenDialog(null);

        if (res == JFileChooser.APPROVE_OPTION) {
            arquivoMedico = fc.getSelectedFiles();
            jTextFieldMedico.setText(arquivoMedico[0].getParent());
            for (int i = 0; i < arquivoMedico.length; i++) {

                tamanhoLinha = tamanhoLinha + arquivoMedico[i].getName().length();

                if (tamanhoLinha <= 90) {

                    linha = linha + arquivoMedico[i].getName() + "; ";

                    tamanhoLinha = linha.length();

                } else {

                    linha = linha + "\n" + arquivoMedico[i].getName() + "; ";
                    tamanhoLinha = 0;

                }

            }

            jTextAreaMedicos.setText(linha);

        } else {
            JOptionPane.showMessageDialog(null, "Você não selecionou nenhum arquivo.");
        }
    }//GEN-LAST:event_jButton1ActionPerformed

    private void jTextFieldBuscaActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jTextFieldBuscaActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jTextFieldBuscaActionPerformed

    private void jButtonBuscarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonBuscarActionPerformed
        preencheGridAssociados();
    }//GEN-LAST:event_jButtonBuscarActionPerformed

    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed
        formWindowOpened(null);
    }//GEN-LAST:event_jButton2ActionPerformed

    private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed
        LerCaminho lc = new LerCaminho();

        lc.RetornoCaminho();

        JFileChooser fc = new JFileChooser();
        FileNameExtensionFilter filter = new FileNameExtensionFilter("Arquivo de Excel", "xls");
        File diretorio = new File(lc.retcaminhoPlaninha);
        String linha = "", texto = "";
        int tamanhoLinha = 0;
        boolean proxima = false;

        fc.setCurrentDirectory(diretorio);
        fc.addChoosableFileFilter(filter);
        fc.setAcceptAllFileFilterUsed(false);
        fc.setFileFilter(filter);
        int res = fc.showOpenDialog(null);

        if (res == JFileChooser.APPROVE_OPTION) {
            File arquivoLocal = fc.getSelectedFile();
            jTextFieldAssociadoViaArq.setText(arquivoLocal.getPath());

        } else {
            JOptionPane.showMessageDialog(null, "Você não selecionou nenhum arquivo.");
        }
    }//GEN-LAST:event_jButton3ActionPerformed

    private void jTextFieldAssociadoViaArqActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jTextFieldAssociadoViaArqActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jTextFieldAssociadoViaArqActionPerformed

    private void jButton11ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton11ActionPerformed
        formWindowOpened3(null);
    }//GEN-LAST:event_jButton11ActionPerformed

    private void jButton10ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton10ActionPerformed
        LerCaminho lc = new LerCaminho();

        lc.RetornoCaminho();

        JFileChooser fc = new JFileChooser();
        FileNameExtensionFilter filter = new FileNameExtensionFilter("Arquivo de Texto", "txt");
        File diretorio = new File(lc.retcaminhoEnvioCloseUp);
        String linha = "", texto = "";
        int tamanhoLinha = 0;
        boolean proxima = false;

        fc.setMultiSelectionEnabled(true);
        fc.setCurrentDirectory(diretorio);
        fc.addChoosableFileFilter(filter);
        fc.setAcceptAllFileFilterUsed(false);
        fc.setFileFilter(filter);
        int res = fc.showOpenDialog(null);

        if (res == JFileChooser.APPROVE_OPTION) {
            arquivoEnvioCloseUp = fc.getSelectedFiles();
            jTextFieldEnvioCloseUp.setText(arquivoEnvioCloseUp[0].getParent());
            for (int i = 0; i < arquivoEnvioCloseUp.length; i++) {

                tamanhoLinha = tamanhoLinha + arquivoEnvioCloseUp[i].getName().length();

                if (tamanhoLinha <= 90) {

                    linha = linha + arquivoEnvioCloseUp[i].getName() + "; ";

                    tamanhoLinha = linha.length();

                } else {

                    linha = linha + "\n" + arquivoEnvioCloseUp[i].getName() + "; ";
                    tamanhoLinha = 0;

                }

            }

            jTextAreaEnvioCloseUp.setText(linha);

        } else {
            JOptionPane.showMessageDialog(null, "Você não selecionou nenhum arquivo.");
        }
    }//GEN-LAST:event_jButton10ActionPerformed

    private void jXDatePickerFimActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jXDatePickerFimActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jXDatePickerFimActionPerformed

    private void jButtonFiltrarEnviosActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonFiltrarEnviosActionPerformed
        formWindowOpenedInformacoes(null);
        //    jTableEnvio.setSelectionMode();

//        GuiUtils.scrollToVisible(jTableEnvio, jTableEnvio.getHeight());
//        GuiUtils.selectAndScroll(jTableEnvio, jTableEnvio.getHeight());
    }//GEN-LAST:event_jButtonFiltrarEnviosActionPerformed

    private void jButton14ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton14ActionPerformed
        LerCaminho lc = new LerCaminho();

        lc.RetornoCaminho();

        JFileChooser fc = new JFileChooser();
        FileNameExtensionFilter filter = new FileNameExtensionFilter("Arquivo de Excel", "xls");
        File diretorio = new File(lc.retCaminhoRetornoCloseUp);
        String linha = "", texto = "";
        int tamanhoLinha = 0;
        boolean proxima = false;

        fc.setCurrentDirectory(diretorio);
        fc.addChoosableFileFilter(filter);
        fc.setAcceptAllFileFilterUsed(false);
        fc.setFileFilter(filter);
        int res = fc.showOpenDialog(null);

        if (res == JFileChooser.APPROVE_OPTION) {
            File arquivoLocal = fc.getSelectedFile();
            jTextFieldCaminhoRetCloseUp.setText(arquivoLocal.getPath());

        } else {
            JOptionPane.showMessageDialog(null, "Você não selecionou nenhum arquivo.");
        }
    }//GEN-LAST:event_jButton14ActionPerformed

    private void jButton13ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton13ActionPerformed


        if (!jTextFieldCaminhoRetCloseUp.getText().equals("")) {

            formWindowOpenedProcRet(null);

        } else {
            JOptionPane.showMessageDialog(null, "Selecione um arquivo de retorno.");
        }
    }//GEN-LAST:event_jButton13ActionPerformed

    private void jTextFieldCaminhoRetCloseUpActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jTextFieldCaminhoRetCloseUpActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jTextFieldCaminhoRetCloseUpActionPerformed

    private void jXDatePickerIniActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jXDatePickerIniActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jXDatePickerIniActionPerformed

    private void jButton12ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton12ActionPerformed
        formWindowOpenedRelatorio(null);
    }//GEN-LAST:event_jButton12ActionPerformed
    public void PreencheTabItenResumo() throws SQLException {
//        String texto = "";
////        int selRow = jTableEnvio.getSelectedRow();
//  //      Object objdescArq = jTableEnvio.getModel().getValueAt(selRow, 1);
//  //      String descArq = objdescArq.toString();
//
//        texto = "Quantidade de Prescrições:\n";
//        Connection con = Conexao.getConnetion();
//        PreparedStatement pstQtdePX = con.prepareStatement(
//                "select "
//                + "COUNT(*) as qtdePX "
//                + "from "
//                + "envio_item "
//                + "join envio on envio_item.id_envio = envio.id_envio "
//                + "where envio.descricao like '" + descArq + "'");
//        ResultSet rsQtdePX = pstQtdePX.executeQuery();
//        while (rsQtdePX.next()) {
//
//            texto = texto + rsQtdePX.getString("qtdePX") + "\n";
//
//        }
//
//        texto = texto + "Total de quantidades:\n";
//        PreparedStatement pstTotalQtde = con.prepareStatement(
//                "select "
//                + "sum(quantidade) as totalQtde "
//                + "from "
//                + "envio_item "
//                + "join envio on envio_item.id_envio = envio.id_envio "
//                + "where envio.descricao like '" + descArq + "'");
//        ResultSet rsTotalQtde = pstTotalQtde.executeQuery();
//        while (rsTotalQtde.next()) {
//
//            texto = texto + rsTotalQtde.getString("totalQtde") + "\n";
//
//        }
//        texto = texto + "Total Removidos:\n";
//        PreparedStatement pstTotalRemovidos = con.prepareStatement(
//                "select "
//                + "count(*) as totalRemovidos "
//                + "from "
//                + "envio_item_deletado "
//                + "join envio on envio_item_deletado.id_envio = envio.id_envio "
//                + "where envio.descricao like '" + descArq + "'");
//        ResultSet rsTotalRemovidos = pstTotalRemovidos.executeQuery();
//        while (rsTotalRemovidos.next()) {
//
//            texto = texto + rsTotalRemovidos.getString("totalRemovidos") + "\n";
//
//        }
//
//
//    //    preencheGridPPV(descArq);
//        jTextAreaResumo.setText(texto);
    }

    public void preencheComboEstado() throws SQLException {

        int count = 1;
        con = Conexao.getConnetion();
        PreparedStatement pstContador = null;
        PreparedStatement pstEstado = null;

        pstEstado = con.prepareStatement("Select distinct(estado) from medicos");
        pstContador = con.prepareStatement("Select distinct(estado) from medicos");

        ResultSet rsEstado = null;
        ResultSet rsContador = null;

        rsEstado = pstEstado.executeQuery();
        rsContador = pstContador.executeQuery();

        while (rsContador.next()) {

            count += 1;

        }

        Object[] itens = new Object[count];

        count = 0;
        itens[0] = "";
        count = 1;
        while (rsEstado.next()) {

            itens[count] = rsEstado.getString(1);
            count += 1;

        }

        DefaultComboBoxModel model = new DefaultComboBoxModel(itens);
        jComboBoxEstado.setModel(model);


    }

    public void importaEnvio() throws IOException, SQLException {

        String linhaErro = "";
        int contagemErro = 0;
        FileOutputStream fos = null;
        File fw = null;

        con = Conexao.getConnetion();
        if (arquivoEnvioCloseUp.length > 0) {

            jProgressBarQtdeItemEnvio.setMinimum(0);
            jProgressBarQtdeItemEnvio.setMaximum(arquivoEnvioCloseUp.length);


            for (int i = 0; i < arquivoEnvioCloseUp.length; i++) {
                jProgressBarQtdeItemEnvio.setValue(i + 1);
                jProgressBarQtdeItemEnvio.setStringPainted(true);
                boolean reprocessar = false;
                String pegaIdArq;
                try {


                    pegaIdArq = arquivoEnvioCloseUp[i].getName().replaceAll("ArquivoErro", "");
                    pegaIdArq = pegaIdArq.replaceAll(".txt", "");
                    Connection conl = Conexao.getConnetion();
                    PreparedStatement pstconl = conl.prepareStatement("select id_envio from envio where id_envio like '" + pegaIdArq + "'");
                    ResultSet rsconl = pstconl.executeQuery();
                    while (rsconl.next()) {

                        reprocessar = true;

                    }


                    boolean criaTxtErro = false;

                    BufferedReader reader = new BufferedReader(new InputStreamReader(
                            new FileInputStream(arquivoEnvioCloseUp[i].getPath()), "ISO-8859-1"));

                    String linha;
                    int qtdeLinhas = 0;
                    while ((linha = reader.readLine()) != null) {
                        if (!linha.isEmpty()) {
                            qtdeLinhas += 1;
                        }
                    }
                    reader.close();
                    jProgressBarLinhasEnvio.setValue(0);
                    jProgressBarLinhasEnvio.setMinimum(0);
                    jProgressBarLinhasEnvio.setMaximum(qtdeLinhas);

                    BufferedReader myBuffer = new BufferedReader(new InputStreamReader(new FileInputStream(arquivoEnvioCloseUp[i].getPath()), "ISO-8859-1"));

                    PreparedStatement pstEnvio = null;

                    Date data = new Date(System.currentTimeMillis());
                    SimpleDateFormat formatador = new SimpleDateFormat("yyyy-MM-dd");
                    String dataFormatada = formatador.format(data);
                    String idEnvio = null;
                    if (!reprocessar) {
                        Update u = new Update();
                        idEnvio = u.getId("id_envio");
                        pstEnvio = con.prepareStatement("insert into envio values ('" + dataFormatada + "','Arquivo de Envio " + idEnvio + "','N')");
                        pstEnvio.executeUpdate();
                        int intid = Integer.parseInt(idEnvio) + 1;
                        u.setUpdateControle("update controle set valor = '" + intid + "' where nome = 'id_envio'");
                    } else {
                        idEnvio = pegaIdArq;
                    }
                    jLabel4.setText("0");
                    jLabel9.setText(String.valueOf(qtdeLinhas));
                    for (int j = 0; j < qtdeLinhas; j++) {
                        PreparedStatement pstEnvioItem = null;
                        jProgressBarLinhasEnvio.setValue(j + 1);
                        jLabel4.setText(String.valueOf(j + 1));
                        jProgressBarLinhasEnvio.setStringPainted(true);
                        linha = myBuffer.readLine();
                        int coluna = 0;

                        String cd_redeArq = "",
                                cd_filArq = "",
                                cnpjArq = "",
                                eanArq = "",
                                ufArq = "",
                                nrcrm = "",
                                tp_crmArq = "",
                                cupomArq = "",
                                diaArq = "01",
                                mesArq = "01",
                                anoArq = "2000",
                                qtdeArq = "",
                                prodArq = "",
                                labArq = "",
                                genArq = "",
                                atendArq = "",
                                id_lab = "",
                                id_prod = "",
                                id_med = "";

                        if (!linha.equals(null)) {
                            if (linha.length() >= 184 && linha.length() <= 190 && !linha.isEmpty()) {

                                cd_redeArq = linha.substring(0, 4);
                                cd_filArq = linha.substring(4, 8);
                                cnpjArq = linha.substring(8, 22);
                                eanArq = linha.substring(22, 42);
                                ufArq = linha.substring(42, 44);
                                nrcrm = linha.substring(47, 54);
                                tp_crmArq = linha.substring(54, 55);
                                cupomArq = linha.substring(55, 65);
                                diaArq = linha.substring(65, 67);
                                mesArq = linha.substring(67, 69);
                                anoArq = linha.substring(69, 73);
                                qtdeArq = linha.substring(73, 79);
                                prodArq = linha.substring(79, 129);
                                labArq = linha.substring(129, 179);
                                genArq = linha.substring(179, 180);
                                atendArq = linha.substring(180);


                                prodArq = prodArq.replaceAll("'", "");
                                prodArq = prodArq.replaceAll(",", "");

                                labArq = labArq.replaceAll("'", "");
                                labArq = labArq.replaceAll(",", "");



                                PreparedStatement pstBuscaProd = con.prepareStatement("select id_produto from produtos where codigo_barras = '" + eanArq + "'");
                                ResultSet rsBuscaProd = pstBuscaProd.executeQuery();
                                id_prod = "";
                                while (rsBuscaProd.next()) {
                                    id_prod = rsBuscaProd.getString(1);

                                }

                                if (id_prod.isEmpty()) {

                                    PreparedStatement pstBuscaLab = con.prepareStatement("select id_laboratorio from laboratorios where descricao = '" + labArq + "'");
                                    ResultSet rsBuscaLab = pstBuscaLab.executeQuery();
                                    id_lab = "";
                                    while (rsBuscaLab.next()) {
                                        id_lab = rsBuscaLab.getString(1);
                                    }

                                    if (id_lab.isEmpty()) {
                                        PreparedStatement pstLab = con.prepareStatement("insert into laboratorios values ('" + labArq + "')");
                                        pstLab.executeUpdate();
                                        PreparedStatement pstBuscaLab2 = con.prepareStatement("select id_laboratorio from laboratorios where descricao = '" + labArq + "'");
                                        ResultSet rsBuscaLab2 = pstBuscaLab2.executeQuery();

                                        while (rsBuscaLab2.next()) {
                                            id_lab = rsBuscaLab2.getString(1);
                                        }
                                    }
                                    PreparedStatement pstProd = con.prepareStatement("insert into produtos values ('" + id_lab + "','" + prodArq + "','" + eanArq + "')");
                                    pstProd.executeUpdate();

                                    PreparedStatement pstBuscaProd2 = con.prepareStatement("select id_produto from produtos where codigo_barras = '" + eanArq + "'");
                                    ResultSet rsBuscaProd2 = pstBuscaProd2.executeQuery();

                                    while (rsBuscaProd2.next()) {
                                        id_prod = rsBuscaProd2.getString(1);

                                    }

                                }
                                PreparedStatement pstBuscaMedico = null;
                                if (!ufArq.equals("")) {
                                    pstBuscaMedico = con.prepareStatement("select id_medico from medicos where crm = '" + nrcrm + "' and estado = '" + ufArq + "'");
                                } else {
                                    pstBuscaMedico = con.prepareStatement("select id_medico from medicos where crm = '" + nrcrm + "'");
                                }
                                ResultSet rsBuscaMedico = pstBuscaMedico.executeQuery();
                                id_med = "";
                                while (rsBuscaMedico.next()) {
                                    id_med = rsBuscaMedico.getString(1);
                                }

                                if (id_med.isEmpty()) {
                                    PreparedStatement pstMedico = con.prepareStatement("insert into medicos values ('NAO CADASTRADO','"
                                            + nrcrm + "','" + tp_crmArq + "','','','" + ufArq + "')");
                                    pstMedico.executeUpdate();

                                    PreparedStatement pstBuscaMedico2 = con.prepareStatement("select id_medico from medicos where crm = '" + nrcrm + "'");
                                    ResultSet rsBuscaMedico2 = pstBuscaMedico2.executeQuery();
                                    id_med = "";
                                    while (rsBuscaMedico2.next()) {
                                        id_med = rsBuscaMedico2.getString(1);
                                    }

                                }

                                PreparedStatement pstItem = con.prepareStatement("insert into envio_item values ('" + idEnvio + "','" + cd_filArq + "','" + id_prod + "','" + id_med + "',"
                                        + "'" + cupomArq + "',"
                                        + "'" + anoArq + "-" + mesArq + "-" + diaArq + "',"
                                        + "'" + qtdeArq + "',"
                                        + "'" + genArq + "',"
                                        + "'" + atendArq + "'"
                                        + ")");
                                pstItem.executeUpdate();

                            } else if (!linha.isEmpty()) {
                                if (!criaTxtErro) {

                                    fw = new File(jTextFieldEnvioCloseUp.getText() + "/ArquivoErro" + idEnvio + ".txt");
                                    fos = new FileOutputStream(fw, false);
                                    System.out.println(contagemErro + "--" + linha.length());
                                    fos.write((linha + System.getProperty("line.separator")).getBytes());
                                    criaTxtErro = true;

                                } else {
                                    System.out.println(contagemErro + "--" + linha.length());
                                    fos.write((linha + System.getProperty("line.separator")).getBytes());
                                }

                                contagemErro += 1;

                            }
                        }
                    }
                    reader.close();
                    fos.close();
                    jTextArea2.setText(jTextArea2.getText() + "\n" + ""
                            + "Arquivo: " + arquivoEnvioCloseUp[i].getName() + "\n"
                            + "Quantidade de erro: " + contagemErro + " de " + qtdeLinhas + "\n"
                            + "Nome do Arquivo: ArquivoErro" + idEnvio + ".txt\n");
                } catch (FileNotFoundException ex) {
                    JOptionPane.showMessageDialog(null, "Arquivo não localizado");
                } catch (IOException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                }

            }

        }
        JOptionPane.showMessageDialog(null, "Arquivo(s) processado(s) com sucesso!");
        jProgressBarQtdeLinhas.setValue(0);
        jProgressBarQtdeSelecionado.setValue(0);
    }

    public void importaMedico() throws IOException, SQLException {
        String estado;
        con = Conexao.getConnetion();
        if (arquivoMedico.length > 0) {

            jProgressBarQtdeSelecionado.setMinimum(0);
            jProgressBarQtdeSelecionado.setMaximum(arquivoMedico.length);


            for (int i = 0; i < arquivoMedico.length; i++) {
                jProgressBarQtdeSelecionado.setValue(i + 1);
                jProgressBarQtdeSelecionado.setStringPainted(true);
                Update up = new Update();
                String delMed = arquivoMedico[i].getName().replaceAll(".txt", "");
                up.setUpdateDireto("delete from medicos where estado = '" + delMed + "'");

                try {
                    // BufferedReader reader = new BufferedReader(new FileReader(arquivoMedico[i].getPath()));  
                    BufferedReader reader = new BufferedReader(new InputStreamReader(
                            new FileInputStream(arquivoMedico[i].getPath()), "ISO-8859-1"));

                    String linha;
                    int qtdeLinhas = 0;
                    while ((linha = reader.readLine()) != null) {
                        qtdeLinhas += 1;
                    }
                    reader.close();
                    jProgressBarQtdeLinhas.setValue(0);
                    jProgressBarQtdeLinhas.setMinimum(0);
                    jProgressBarQtdeLinhas.setMaximum(qtdeLinhas);

//                reader = new BufferedReader(new InputStreamReader(
//                new FileInputStream(arquivoMedico[i].getPath()), "UTF-8"));
                    BufferedReader myBuffer = new BufferedReader(new InputStreamReader(new FileInputStream(arquivoMedico[i].getPath()), "ISO-8859-1"));
                    PreparedStatement pstMedico = null;


                    for (int j = 0; j < qtdeLinhas; j++) {
                        jProgressBarQtdeLinhas.setValue(j + 1);
                        jProgressBarQtdeLinhas.setStringPainted(true);
                        linha = myBuffer.readLine();
                        int coluna = 0;
                        String crm = "", siglaEstado = "", nome = "", ordem = "", status = "", id_medico;

                        Update u = new Update();
                        for (int k = 0; k < linha.length(); k++) {
                            if (coluna == 0) {
                                if (linha.charAt(k) != '!') {
                                    crm = crm + linha.charAt(k);
                                } else {
                                    coluna = 1;
                                }
                            } else if (coluna == 1) {
                                if (linha.charAt(k) != '!') {
                                    siglaEstado = siglaEstado + linha.charAt(k);
                                } else {
                                    coluna = 2;
                                }
                            } else if (coluna == 2) {
                                if (linha.charAt(k) != '!') {
                                    nome = nome + linha.charAt(k);
                                } else {
                                    coluna = 3;
                                }
                            } else if (coluna == 3) {
                                if (linha.charAt(k) != '!') {
                                    ordem = ordem + linha.charAt(k);
                                } else {
                                    coluna = 4;
                                }
                            } else if (coluna == 4) {
                                if (linha.charAt(k) != '!') {
                                    status = status + linha.charAt(k);
                                } else {
                                    coluna = 5;
                                }
                            }

                        }

                        nome = nome.replaceAll("'", "");

                        pstMedico = con.prepareStatement("insert into medicos values ('" + nome + "','" + crm + "','M','" + ordem + "','" + status + "','" + siglaEstado + "')");
                        pstMedico.executeUpdate();

                    }
                    reader.close();


                } catch (FileNotFoundException ex) {
                    JOptionPane.showMessageDialog(null, "Arquivo não localizado");
                } catch (IOException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                }

            }

        }
        JOptionPane.showMessageDialog(null, "Arquivo(s) processado(s) com sucesso!");
        jProgressBarQtdeLinhas.setValue(0);
        jProgressBarQtdeSelecionado.setValue(0);
    }

    public void preencheGridImportacoesResumo() {
        ResultSet rsQtdeDeLojas = null;
        PreparedStatement pstQtdeDeLojas = null;

        ResultSet rsQtdeDeLojasDetalhe = null;
        PreparedStatement pstQtdeDeLojasDetalhe = null;

        ResultSet rsQtdeDeLojasFora = null;
        PreparedStatement pstQtdeDeLojasFora = null;

        ResultSet rsQtdeDeLojasForaDetalhe = null;
        PreparedStatement pstQtdeDeLojasForaDetalhe = null;

        ResultSet rsQtdeDeProdutos = null;
        PreparedStatement pstQtdeDeProdutos = null;

        ResultSet rsQtdeDeProdutosDetalhe = null;
        PreparedStatement pstQtdeDeProdutosDetalhe = null;

        ResultSet rsQtdeDeLaboratorios = null;
        PreparedStatement pstQtdeDeLaboratorios = null;

        ResultSet rsQtdeDeLaboratoriosDetalhes = null;
        PreparedStatement pstQtdeDeLaboratoriosDetalhes = null;

        ResultSet rsQtdeDeMedicos = null;
        PreparedStatement pstQtdeDeMedicos = null;

        ResultSet rsQtdeDeMedicosDetalhes = null;
        PreparedStatement pstQtdeDeMedicosDetalhes = null;

        ResultSet rsQtdeDePrescricoes = null;
        PreparedStatement pstQtdeDePrescricoes = null;

        ResultSet rsQtdeDePrescricoesDel = null;
        PreparedStatement pstQtdeDePrescricoesDel = null;

        Connection conEnvios = Conexao.getConnetion();
        Connection conEnviosDetalhes = Conexao.getConnetion();

        SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd");
        String data1 = formato.format(jXDatePickerIni.getDate());
        String data2 = formato.format(jXDatePickerFim.getDate());



        try {

            if (data1 != null && data2 != null) {

                pstQtdeDeLojas = conEnvios.prepareStatement(""
                        + "select count(distinct(Associados.razão_social)) "
                        + "from "
                        + "envio_item "
                        + "join Associados on envio_item.id_filial = Associados.id_associados "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "'");

                pstQtdeDeLojasDetalhe = conEnviosDetalhes.prepareStatement(""
                        + "select Associados.razão_social,COUNT(Associados.razão_social) as Prescrições "
                        + "from "
                        + "envio_item "
                        + "join Associados on envio_item.id_filial = Associados.id_associados "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                        + "group by Associados.razão_social order by Prescrições desc ");


                pstQtdeDeLojasFora = conEnvios.prepareStatement(""
                        + "Select count(*) from associados where associados.id_associados not in("
                        + "select distinct(envio_item.id_filial) "
                        + "from "
                        + "envio_item "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "')");

                pstQtdeDeLojasForaDetalhe = conEnviosDetalhes.prepareStatement(""
                        + "Select Associados.razão_social from associados where associados.id_associados not in( "
                        + "select distinct(envio_item.id_filial) "
                        + "from "
                        + "envio_item "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "') "
                        + "order by Associados.razão_social");


                pstQtdeDeProdutos = conEnvios.prepareStatement(""
                        + "select count(distinct(produtos.id_produto)) "
                        + "from "
                        + "envio_item "
                        + "join Produtos on envio_item.id_produto = produtos.id_produto "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "'");

                pstQtdeDeProdutosDetalhe = conEnviosDetalhes.prepareStatement(""
                        + "select distinct(produtos.descricao),COUNT(produtos.descricao) as qtdePX "
                        + "from "
                        + "envio_item "
                        + "join Produtos on envio_item.id_produto = produtos.id_produto "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                        + "group by produtos.descricao order by qtdePX desc ");

                pstQtdeDeLaboratorios = conEnvios.prepareStatement(""
                        + "select count(distinct(laboratorios.id_laboratorio)) "
                        + "from "
                        + "envio_item "
                        + "join Produtos on envio_item.id_produto = produtos.id_produto "
                        + "join laboratorios on produtos.id_laboratorio = laboratorios.id_laboratorio "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "'");

                pstQtdeDeLaboratoriosDetalhes = conEnviosDetalhes.prepareStatement(""
                        + "select distinct(laboratorios.descricao),COUNT(laboratorios.descricao) as QtdePX "
                        + "from "
                        + "envio_item "
                        + "join Produtos on envio_item.id_produto = produtos.id_produto "
                        + "join laboratorios on produtos.id_laboratorio = laboratorios.id_laboratorio "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                        + "group by laboratorios.descricao order by QtdePX desc");

                pstQtdeDeMedicos = conEnvios.prepareStatement(""
                        + "select count(distinct(envio_item.id_medico)) "
                        + "from "
                        + "envio_item "
                        + "join medicos on envio_item.id_medico = medicos.id_medico "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "'");

                pstQtdeDeMedicosDetalhes = conEnviosDetalhes.prepareStatement(""
                        + "select distinct(medicos.nome),COUNT(medicos.nome) as QtdePX "
                        + "from "
                        + "envio_item "
                        + "join medicos on envio_item.id_medico = medicos.id_medico "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                        + "group by medicos.nome order by QtdePX desc ");

                pstQtdeDePrescricoes = conEnvios.prepareStatement(""
                        + "select count(*) "
                        + "from "
                        + "envio_item "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "'");

                pstQtdeDePrescricoesDel = conEnviosDetalhes.prepareStatement(""
                        + "select count(*) "
                        + "from "
                        + "envio_item "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "'");

            }
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            jProgressBarInfo.setMinimum(0);
            jProgressBarInfo.setMaximum(11);

            rsQtdeDeLojas = pstQtdeDeLojas.executeQuery();
            jProgressBarInfo.setValue(1);
            jProgressBarInfo.setStringPainted(true);
            rsQtdeDeLojasDetalhe = pstQtdeDeLojasDetalhe.executeQuery();
            jProgressBarInfo.setValue(2);
            jProgressBarInfo.setStringPainted(true);
            rsQtdeDeLojasFora = pstQtdeDeLojasFora.executeQuery();
            jProgressBarInfo.setValue(3);
            jProgressBarInfo.setStringPainted(true);
            rsQtdeDeLojasForaDetalhe = pstQtdeDeLojasForaDetalhe.executeQuery();
            jProgressBarInfo.setValue(4);
            jProgressBarInfo.setStringPainted(true);
            rsQtdeDeProdutos = pstQtdeDeProdutos.executeQuery();
            jProgressBarInfo.setValue(5);
            jProgressBarInfo.setStringPainted(true);
            rsQtdeDeProdutosDetalhe = pstQtdeDeProdutosDetalhe.executeQuery();
            jProgressBarInfo.setValue(6);
            jProgressBarInfo.setStringPainted(true);
            rsQtdeDeLaboratorios = pstQtdeDeLaboratorios.executeQuery();
            jProgressBarInfo.setValue(7);
            jProgressBarInfo.setStringPainted(true);
            rsQtdeDeLaboratoriosDetalhes = pstQtdeDeLaboratoriosDetalhes.executeQuery();
            jProgressBarInfo.setValue(8);
            jProgressBarInfo.setStringPainted(true);
            rsQtdeDeMedicos = pstQtdeDeMedicos.executeQuery();
            jProgressBarInfo.setValue(9);
            jProgressBarInfo.setStringPainted(true);
            rsQtdeDeMedicosDetalhes = pstQtdeDeMedicosDetalhes.executeQuery();
            jProgressBarInfo.setValue(10);
            jProgressBarInfo.setStringPainted(true);

            rsQtdeDePrescricoes = pstQtdeDePrescricoes.executeQuery();
            jProgressBarInfo.setValue(11);
            jProgressBarInfo.setStringPainted(true);
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }

        String[] tableColumnsName = {"Campo", "Total"};
        String[] tableColumnsNameDLojasDentro = {"Razão Social", "Prescrições"};
        String[] tableColumnsNameDLojasFora = {"Razão Social"};
        String[] tableColumnsNameDProdutos = {"Produto", "Prescrições"};
        String[] tableColumnsNameDLaboratorio = {"Laboratório", "Prescrições"};
        String[] tableColumnsNameDMedico = {"Médico", "Prescrições"};

        DefaultTableModel aModel = (DefaultTableModel) jTableInformacoesResumidas.getModel();
        aModel.setNumRows(0);
        aModel.setColumnIdentifiers(tableColumnsName);
        DefaultTableModel aModelDLojasDentro = (DefaultTableModel) jTableDLojasDentro.getModel();
        aModelDLojasDentro.setNumRows(0);
        aModelDLojasDentro.setColumnIdentifiers(tableColumnsNameDLojasDentro);
        DefaultTableModel aModelDLojasFora = (DefaultTableModel) jTableDLojasFora.getModel();
        aModelDLojasFora.setNumRows(0);
        aModelDLojasFora.setColumnIdentifiers(tableColumnsNameDLojasFora);
        DefaultTableModel aModelDProdutos = (DefaultTableModel) jTableDProduto.getModel();
        aModelDProdutos.setNumRows(0);
        aModelDProdutos.setColumnIdentifiers(tableColumnsNameDProdutos);
        DefaultTableModel aModelDLaboratorio = (DefaultTableModel) jTableDLaboratorio.getModel();
        aModelDLaboratorio.setNumRows(0);
        aModelDLaboratorio.setColumnIdentifiers(tableColumnsNameDLaboratorio);
        DefaultTableModel aModelDMedico = (DefaultTableModel) jTableDMedicos.getModel();
        aModelDMedico.setNumRows(0);
        aModelDMedico.setColumnIdentifiers(tableColumnsNameDMedico);


        jTableInformacoesResumidas.setAutoResizeMode(jTableInformacoesResumidas.AUTO_RESIZE_OFF);
        jTableInformacoesResumidas.getColumnModel().getColumn(0).setPreferredWidth(200);
        jTableInformacoesResumidas.getColumnModel().getColumn(1).setPreferredWidth(150);

        jTableDLojasDentro.setAutoResizeMode(jTableDLojasDentro.AUTO_RESIZE_OFF);
        jTableDLojasDentro.getColumnModel().getColumn(0).setPreferredWidth(400);
        jTableDLojasDentro.getColumnModel().getColumn(1).setPreferredWidth(150);

        jTableDLojasFora.setAutoResizeMode(jTableDLojasDentro.AUTO_RESIZE_OFF);
        jTableDLojasFora.getColumnModel().getColumn(0).setPreferredWidth(550);

        jTableDProduto.setAutoResizeMode(jTableDProduto.AUTO_RESIZE_OFF);
        jTableDProduto.getColumnModel().getColumn(0).setPreferredWidth(400);
        jTableDProduto.getColumnModel().getColumn(1).setPreferredWidth(150);

        jTableDLaboratorio.setAutoResizeMode(jTableDLaboratorio.AUTO_RESIZE_OFF);
        jTableDLaboratorio.getColumnModel().getColumn(0).setPreferredWidth(400);
        jTableDLaboratorio.getColumnModel().getColumn(1).setPreferredWidth(150);

        jTableDMedicos.setAutoResizeMode(jTableDMedicos.AUTO_RESIZE_OFF);
        jTableDMedicos.getColumnModel().getColumn(0).setPreferredWidth(400);
        jTableDMedicos.getColumnModel().getColumn(1).setPreferredWidth(150);

        int colNo = 2;
        try {
            while (rsQtdeDeLojas.next()) {
                Object[] objects = new Object[colNo];
                objects[0] = "Quantidade de lojas dentro";
                objects[1] = rsQtdeDeLojas.getObject(1);

                aModel.addRow(objects);

            }
            while (rsQtdeDeLojasFora.next()) {
                Object[] objects = new Object[colNo];
                objects[0] = "Quantidade de lojas fora";
                objects[1] = rsQtdeDeLojasFora.getObject(1);

                aModel.addRow(objects);

            }

            while (rsQtdeDeProdutos.next()) {
                Object[] objects = new Object[colNo];
                objects[0] = "Quantidade de Produtos";
                objects[1] = rsQtdeDeProdutos.getObject(1);

                aModel.addRow(objects);

            }
            while (rsQtdeDeLaboratorios.next()) {
                Object[] objects = new Object[colNo];
                objects[0] = "Quantidade de Laboratórios";
                objects[1] = rsQtdeDeLaboratorios.getObject(1);

                aModel.addRow(objects);

            }
            while (rsQtdeDeMedicos.next()) {
                Object[] objects = new Object[colNo];
                objects[0] = "Quantidade de Médicos";
                objects[1] = rsQtdeDeMedicos.getObject(1);

                aModel.addRow(objects);

            }
            while (rsQtdeDePrescricoes.next()) {
                Object[] objects = new Object[colNo];
                objects[0] = "Quantidade de Prescrições";
                objects[1] = rsQtdeDePrescricoes.getObject(1);

                aModel.addRow(objects);

            }

            java.sql.ResultSetMetaData rsmdLojasDetalhe = null;
            rsmdLojasDetalhe = rsQtdeDeLojasDetalhe.getMetaData();

            colNo = rsmdLojasDetalhe.getColumnCount();


            while (rsQtdeDeLojasDetalhe.next()) {
                Object[] objects = new Object[colNo];
                for (int i = 0; i < colNo; i++) {
                    objects[i] = rsQtdeDeLojasDetalhe.getObject(i + 1);
                }
                aModelDLojasDentro.addRow(objects);

            }
            jTableDLojasDentro.setModel(aModelDLojasDentro);

            colNo = 1;
            while (rsQtdeDeLojasForaDetalhe.next()) {
                Object[] objects = new Object[colNo];

                objects[0] = rsQtdeDeLojasForaDetalhe.getObject(1);

                aModelDLojasFora.addRow(objects);

            }
            jTableDLojasFora.setModel(aModelDLojasFora);

            java.sql.ResultSetMetaData rsmdProduto = null;
            rsmdProduto = rsQtdeDeProdutosDetalhe.getMetaData();

            colNo = rsmdProduto.getColumnCount();


            while (rsQtdeDeProdutosDetalhe.next()) {
                Object[] objects = new Object[colNo];
                for (int i = 0; i < colNo; i++) {
                    objects[i] = rsQtdeDeProdutosDetalhe.getObject(i + 1);
                }
                aModelDProdutos.addRow(objects);

            }
            jTableDProduto.setModel(aModelDProdutos);

            java.sql.ResultSetMetaData rsmdLaboratorio = null;
            rsmdLaboratorio = rsQtdeDeLaboratoriosDetalhes.getMetaData();

            colNo = rsmdLaboratorio.getColumnCount();


            while (rsQtdeDeLaboratoriosDetalhes.next()) {
                Object[] objects = new Object[colNo];
                for (int i = 0; i < colNo; i++) {
                    objects[i] = rsQtdeDeLaboratoriosDetalhes.getObject(i + 1);
                }
                aModelDLaboratorio.addRow(objects);

            }
            jTableDLaboratorio.setModel(aModelDLaboratorio);

            java.sql.ResultSetMetaData rsmdMedico = null;
            rsmdMedico = rsQtdeDeMedicosDetalhes.getMetaData();

            colNo = rsmdMedico.getColumnCount();


            while (rsQtdeDeMedicosDetalhes.next()) {
                Object[] objects = new Object[colNo];
                for (int i = 0; i < colNo; i++) {
                    objects[i] = rsQtdeDeMedicosDetalhes.getObject(i + 1);
                }
                aModelDMedico.addRow(objects);

            }
            jTableDMedicos.setModel(aModelDMedico);

        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }


//        TableCellRenderer renderer = new LineSelectionTableCellRenderer();
//
//        for (int c = 0; c < jTableInformacoesResumidas.getColumnCount(); c++) {
//            jTableInformacoesResumidas.setDefaultRenderer(jTableInformacoesResumidas.getColumnClass(c), renderer);
//        }
        jTableInformacoesResumidas.setModel(aModel);



    }

    public void preencheGridAssociados() {

        ResultSet rsAssociados = null;

        PreparedStatement pst = null;

        Connection con = Conexao.getConnetion();

        try {

            if (jTextFieldBusca.getText().equals("")) {

                pst = con.prepareStatement("SELECT "
                        + "id_associados, "
                        + "razão_social, "
                        + "cnpj, "
                        + "endereco, "
                        + "bairro.nome as nome_bairro, "
                        + "cidade.nome as nome_cidade "
                        + "FROM "
                        + "associados "
                        + "left outer join bairro on associados.id_bairro = bairro.id_bairro "
                        + "left outer join cidade on associados.id_cidade = cidade.id_cidade ");
            } else {
                pst = con.prepareStatement("SELECT "
                        + "id_associados, "
                        + "razão_social, "
                        + "cnpj, "
                        + "endereco, "
                        + "bairro.nome as nome_bairro, "
                        + "cidade.nome as nome_cidade "
                        + "FROM "
                        + "associados "
                        + "left outer join bairro on associados.id_bairro = bairro.id_bairro "
                        + "left outer join cidade on associados.id_cidade = cidade.id_cidade "
                        + "where "
                        + "razao_social like '" + jTextFieldBusca.getText() + "%' "
                        + "or cnpj = '" + jTextFieldBusca.getText() + "'");
            }
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            rsAssociados = pst.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }


        String[] tableColumnsName = {"Código", "Razão Social", "CNPJ", "Endereço", "Bairro", "Cidade"};

        DefaultTableModel aModel = (DefaultTableModel) jTableAssociados.getModel();
        aModel.setNumRows(0);
        aModel.setColumnIdentifiers(tableColumnsName);

        java.sql.ResultSetMetaData rsmd = null;
        try {
            rsmd = rsAssociados.getMetaData();
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }
        jTableAssociados.setAutoResizeMode(jTableAssociados.AUTO_RESIZE_OFF);
        jTableAssociados.getColumnModel().getColumn(0).setPreferredWidth(60);
        jTableAssociados.getColumnModel().getColumn(1).setPreferredWidth(300);
        jTableAssociados.getColumnModel().getColumn(2).setPreferredWidth(120);
        jTableAssociados.getColumnModel().getColumn(3).setPreferredWidth(300);
        jTableAssociados.getColumnModel().getColumn(4).setPreferredWidth(170);
        jTableAssociados.getColumnModel().getColumn(5).setPreferredWidth(170);



        int colNo = 0;
        try {
            colNo = rsmd.getColumnCount() + 1;
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            while (rsAssociados.next()) {
                Object[] objects = new Object[colNo];
                for (int i = 0; i < colNo - 1; i++) {
                    objects[i] = rsAssociados.getObject(i + 1);
                }
                objects[colNo - 1] = "P";
                aModel.addRow(objects);

            }
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }

        jTableAssociados.setModel(aModel);




    }

    public void preencheGridPPV(String descArq) throws SQLException {

        ResultSet rsPPV = null;

        PreparedStatement pstPPV = null;

        Connection conPPV = Conexao.getConnetion();


        pstPPV = conPPV.prepareStatement("select \n"
                + "produtos.descricao  ,\n"
                + " count(envio_item.id_produto) as tot,\n"
                + " SUM(envio_item.quantidade) as totQTDE\n"
                + " from envio_item\n"
                + " join envio on envio_item.id_envio = envio.id_envio\n"
                + " join produtos on envio_item.id_produto = produtos.id_produto \n"
                + "where envio.descricao like '" + descArq + "'  \n"
                + "group by envio_item.id_produto,produtos.descricao\n"
                + "order by tot desc");

        rsPPV = pstPPV.executeQuery();
        String[] tableColumnsName = {"Descrição", "Total PX", "Total QTDE"};

        //      DefaultTableModel aModel = (DefaultTableModel) jTableProdutosPorPX.getModel();
        //      aModel.setNumRows(0);
        //      aModel.setColumnIdentifiers(tableColumnsName);

        java.sql.ResultSetMetaData rsmd = null;
        try {
            rsmd = rsPPV.getMetaData();
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }
//        jTableProdutosPorPX.setAutoResizeMode(jTableProdutosPorPX.AUTO_RESIZE_OFF);
//        jTableProdutosPorPX.getColumnModel().getColumn(0).setPreferredWidth(350);
//        jTableProdutosPorPX.getColumnModel().getColumn(1).setPreferredWidth(75);
//        jTableProdutosPorPX.getColumnModel().getColumn(2).setPreferredWidth(75);

        int colNo = 0;
        try {
            colNo = rsmd.getColumnCount() + 1;
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            while (rsPPV.next()) {
                Object[] objects = new Object[colNo];
                for (int i = 0; i < colNo - 1; i++) {
                    objects[i] = rsPPV.getObject(i + 1);
                }
                objects[colNo - 1] = "P";
                //             aModel.addRow(objects);

            }
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }

        //   jTableProdutosPorPX.setModel(aModel);

    }

    public void preencheGridMedicos() {

        ResultSet rsMedicos = null;

        PreparedStatement pst = null;

        Connection con = Conexao.getConnetion();

        try {
            String obj = jComboBoxEstado.getSelectedItem().toString();
            if (jTextFieldBuscaMedico.getText().equals("") && obj.equals("")) {

                pst = con.prepareStatement("SELECT "
                        + "nome,crm,tipo_crm,ordem,status,estado "
                        + "FROM "
                        + "medicos order by estado,nome");
            } else {
                if (jComboBoxEstado.getSelectedIndex() == 0) {
                    pst = con.prepareStatement("SELECT "
                            + "nome,crm,tipo_crm,ordem,status,estado "
                            + "FROM "
                            + "medicos "
                            + "where "
                            + "(nome like '" + jTextFieldBuscaMedico.getText() + "%' "
                            + "or crm like '" + jTextFieldBuscaMedico.getText() + "%')");
                } else {
                    pst = con.prepareStatement("SELECT "
                            + "nome,crm,tipo_crm,ordem,status,estado "
                            + "FROM "
                            + "medicos "
                            + "where "
                            + "(nome like '" + jTextFieldBuscaMedico.getText() + "%' "
                            + "or crm like '" + jTextFieldBuscaMedico.getText() + "%')"
                            + "and estado = '" + jComboBoxEstado.getSelectedItem().toString() + "' ");
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            rsMedicos = pst.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }


        String[] tableColumnsName = {"Nome", "CRM", "Tipo", "Ordem", "Status", "Estado"};

        DefaultTableModel aModel = (DefaultTableModel) jTableMedicos.getModel();
        aModel.setNumRows(0);
        aModel.setColumnIdentifiers(tableColumnsName);

        java.sql.ResultSetMetaData rsmd = null;
        try {
            rsmd = rsMedicos.getMetaData();
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }
        jTableMedicos.setAutoResizeMode(jTableMedicos.AUTO_RESIZE_OFF);
        jTableMedicos.getColumnModel().getColumn(0).setPreferredWidth(445);
        jTableMedicos.getColumnModel().getColumn(1).setPreferredWidth(110);
        jTableMedicos.getColumnModel().getColumn(2).setPreferredWidth(120);
        jTableMedicos.getColumnModel().getColumn(3).setPreferredWidth(190);
        jTableMedicos.getColumnModel().getColumn(4).setPreferredWidth(170);
        jTableMedicos.getColumnModel().getColumn(5).setPreferredWidth(100);



        int colNo = 0;
        try {
            colNo = rsmd.getColumnCount() + 1;
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            while (rsMedicos.next()) {
                Object[] objects = new Object[colNo];
                for (int i = 0; i < colNo - 1; i++) {
                    objects[i] = rsMedicos.getObject(i + 1);
                }
                objects[colNo - 1] = "P";
                aModel.addRow(objects);

            }
        } catch (SQLException ex) {
            Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
        }

        jTableMedicos.setModel(aModel);




    }

    public void importaAssociados() {
        if (!jTextFieldAssociadoViaArq.getText().isEmpty()) {
            WorkbookSettings conf = new WorkbookSettings();
            conf.setEncoding("ISO-8859-1");

            Workbook workbook = null;

            try {
                workbook = Workbook.getWorkbook(new File(jTextFieldAssociadoViaArq.getText()), conf);
            } catch (IOException ex) {
                Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
            } catch (BiffException ex) {
                Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
            }

            Sheet sheet = workbook.getSheet(1);
            int linhas = sheet.getRows();
            System.out.println("Iniciando a leitura da planilha XLS:");


            jProgressBarImportAssoc.setMinimum(0);
            jProgressBarImportAssoc.setMaximum(linhas);
            for (int i = 1; i < linhas; i++) {
                jProgressBarImportAssoc.setValue(i + 1);
                jProgressBarImportAssoc.setStringPainted(true);
                Cell bairro = sheet.getCell(6, i);
                Cell cidade = sheet.getCell(1, i);
                Cell razaosocial = sheet.getCell(2, i);
                Cell telefone = sheet.getCell(3, i);
                Cell contato = sheet.getCell(4, i);
                Cell endereco = sheet.getCell(5, i);
                Cell cep = sheet.getCell(7, i);
                Cell ie = sheet.getCell(8, i);
                Cell cnpj = sheet.getCell(9, i);
                Cell codigofil = sheet.getCell(0, i);

                String nomeBairro = bairro.getContents();
                String nomeCidade = cidade.getContents();
                String nomeRazaoSocial = razaosocial.getContents();
                String Telefone = telefone.getContents();
                String Contato = contato.getContents();
                String Endereco = endereco.getContents();
                String Cep = cep.getContents();
                String IE = ie.getContents();
                String CNPJ = cnpj.getContents();
                String CodigoFil = codigofil.getContents();

                String idBairro = null;
                String idCidade = null;
                Update u = new Update();
                String contac = "";
                for (int j = 0; j < CNPJ.length(); j++) {
                    if ((CNPJ.charAt(j) == '0')
                            || (CNPJ.charAt(j) == '1')
                            || (CNPJ.charAt(j) == '2')
                            || (CNPJ.charAt(j) == '3')
                            || (CNPJ.charAt(j) == '4')
                            || (CNPJ.charAt(j) == '5')
                            || (CNPJ.charAt(j) == '6')
                            || (CNPJ.charAt(j) == '7')
                            || (CNPJ.charAt(j) == '8')
                            || (CNPJ.charAt(j) == '9')) {
                        contac = contac + CNPJ.charAt(j);
                    }
                }
                CNPJ = contac;


                try {
                    if (u.getExiste("id_bairro", "bairro", "nome = '" + nomeBairro + "'")) {

                        idBairro = u.getValor("id_bairro", "bairro", "nome = '" + nomeBairro + "'");

                    } else {
                        if (!nomeBairro.isEmpty()) {
                            idBairro = u.getId("id_bairro");

                            u.setUpdateDireto("INSERT INTO bairro VALUES(" + idBairro + ",'"
                                    + nomeBairro + "')");
                            int proxVal = Integer.parseInt(idBairro);
                            proxVal = proxVal + 1;
                            u.setUpdateDireto("update controle set valor = " + proxVal + " where nome = 'id_bairro'");
                        }
                    }

                    if (u.getExiste("id_cidade", "cidade", "nome = '" + nomeCidade + "'")) {

                        idCidade = u.getValor("id_cidade", "cidade", "nome = '" + nomeCidade + "'");

                    } else {
                        if (!nomeCidade.isEmpty()) {
                            idCidade = u.getId("id_cidade");
                            u.setUpdateDireto("INSERT INTO cidade VALUES(" + idCidade + ",0,'"
                                    + nomeCidade + "')");
                            int proxVal = Integer.parseInt(idCidade);
                            proxVal = proxVal + 1;
                            u.setUpdateDireto("update controle set valor = " + proxVal + " where nome = 'id_cidade'");
                        }
                    }

                    if (!u.getExiste("id_associados", "associados", "id_associados = '" + CodigoFil + "'")) {

                        if (!CodigoFil.isEmpty()) {
                            u.setUpdateDireto("INSERT associados(id_associados,razão_social,id_cidade,"
                                    + "telefone,contato,endereco,id_bairro,"
                                    + "cep,inscricao_estadual,cnpj,id_rede) "
                                    + "VALUES("
                                    + CodigoFil + ",'"
                                    + nomeRazaoSocial + "',"
                                    + idCidade + ",'"
                                    + Telefone + "','"
                                    + Contato + "','"
                                    + Endereco + "',"
                                    + idBairro + ",'"
                                    + Cep + "','"
                                    + IE + "','"
                                    + CNPJ + "',"
                                    + "'0440'"
                                    + ")");

                        }

                    } else {
                        if (!CodigoFil.isEmpty()) {
                            u.setUpdateDireto("update associados set "
                                    + "razão_social = '" + nomeRazaoSocial + "',"
                                    + "id_cidade = " + idCidade + ","
                                    + "telefone = '" + Telefone + "',"
                                    + "contato = '" + Contato + "',"
                                    + "endereco = '" + Endereco + "',"
                                    + "id_bairro = " + idBairro + ","
                                    + "cep = '" + Cep + "',"
                                    + "inscricao_estadual = '" + IE + "',"
                                    + "cnpj = '" + CNPJ + "' "
                                    + "where id_associados = " + CodigoFil);
                        }


                    }

                } catch (IOException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                }


            }


            workbook.close();

            JOptionPane.showMessageDialog(null, "Arquivo importado com sucesso!");
            jProgressBarImportAssoc.setValue(0);


        }
    }

    public void processarRetorno() throws SQLException {
        TelaSelecionaArqEnv tsae = new TelaSelecionaArqEnv(this, true);
        tsae.setLocationRelativeTo(null);
        tsae.setVisible(true);

        if (tsae.id_envio != 0) {

            int QtdeProcessados = 0, linhasLidas = 0, QtdeZeradas = 0;
            if (!jTextFieldCaminhoRetCloseUp.getText().isEmpty()) {
                WorkbookSettings conf = new WorkbookSettings();
                conf.setEncoding("ISO-8859-1");

                Workbook workbook = null;

                try {
                    workbook = Workbook.getWorkbook(new File(jTextFieldCaminhoRetCloseUp.getText()), conf);
                } catch (IOException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                } catch (BiffException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                }

                Sheet sheet = workbook.getSheet(6);
                int linhas = sheet.getRows();

                linhasLidas = linhas;
                JLabelTotal.setText(String.valueOf(linhas));
                System.out.println("Iniciando a leitura da planilha XLS:");


                jProgressBarProcRetorno.setMinimum(0);
                jProgressBarProcRetorno.setMaximum(linhas);
                jProgressBarProcRetorno.setValue(0);


                Connection conBuscaIdEnvio = Conexao.getConnetion();


                PreparedStatement pstAtualizaEnvio = conBuscaIdEnvio.prepareStatement(""
                        + "update envio set retorno = 'S' where id_envio = " + tsae.id_envio);
                pstAtualizaEnvio.executeUpdate();

                for (int i = 2; i < linhas; i++) {

                    System.out.println(i);
                    jLabelLidas.setText(String.valueOf(i + 1));
                    jProgressBarProcRetorno.setValue(i + 1);
                    jProgressBarProcRetorno.setStringPainted(true);
                    Cell filial = sheet.getCell(0, i);
                    Cell UFcrm = sheet.getCell(2, i);
                    Cell ean = sheet.getCell(3, i);
                    Cell descricaoProd = sheet.getCell(4, i);
                    Cell qtdePX = sheet.getCell(5, i);

                    String strfilial = filial.getContents();
                    String strUFcrm = UFcrm.getContents();
                    String strEAN = ean.getContents();
                    String strdescricaoProd = descricaoProd.getContents();
                    String strqtdePX = qtdePX.getContents();

                    if (!strqtdePX.equals("0")) {
                        QtdeProcessados++;

                        String id_filial = null;

                        boolean achouNum = false;
                        for (int j = 0; j < strfilial.length(); j++) {
                            if (!achouNum) {
                                if ((strfilial.charAt(j) == '0')
                                        || (strfilial.charAt(j) == '1')
                                        || (strfilial.charAt(j) == '2')
                                        || (strfilial.charAt(j) == '3')
                                        || (strfilial.charAt(j) == '4')
                                        || (strfilial.charAt(j) == '5')
                                        || (strfilial.charAt(j) == '6')
                                        || (strfilial.charAt(j) == '7')
                                        || (strfilial.charAt(j) == '8')
                                        || (strfilial.charAt(j) == '9')) {

                                    id_filial = strfilial.substring(j, j + 3);
                                    achouNum = true;

                                }
                            }

                        }
                        String zeros = "";
                        String crm = strUFcrm.substring(2);
                        String uf = strUFcrm.substring(0, 2);
                        for (int j = 0; j < 20 - strEAN.length(); j++) {
                            zeros = zeros + "0";
                        }

                        strEAN = zeros + strEAN;

                        String id_envio_item = null;
                        Connection conver = Conexao.getConnetion();
                        PreparedStatement pstVerifica = conver.prepareStatement(""
                                + "select id_envio_item from envio_item "
                                + " join produtos on envio_item.id_produto = produtos.id_produto "
                                + " join medicos on envio_item.id_medico = medicos.id_medico "
                                + "where produtos.codigo_barras like '" + strEAN + "' and "
                                + "medicos.crm = " + crm + " and medicos.estado = '" + uf + "' and id_envio = " + tsae.id_envio
                                + " and id_filial = " + id_filial);

                        ResultSet rsVerifica = pstVerifica.executeQuery();
                        boolean deletou = false;
                        while (rsVerifica.next()) {

                            id_envio_item = rsVerifica.getString("id_envio_item");

                            PreparedStatement pstInsereDel = conBuscaIdEnvio.prepareStatement(""
                                    + "insert into envio_item_deletado select * from envio_item where "
                                    + "id_envio_item = " + id_envio_item);
                            pstInsereDel.executeUpdate();

                            deletou = true;
                        }
                        conver.close();
                        if (deletou) {


                            PreparedStatement pstDel = conBuscaIdEnvio.prepareStatement(""
                                    + "delete from envio_item where "
                                    + "id_envio_item = " + id_envio_item);
                            pstDel.executeUpdate();


                        }
                    } else {
                        QtdeZeradas++;
                    }
                }


                workbook.close();

                JOptionPane.showMessageDialog(null, "Arquivo importado com sucesso!\nQtdes de linhas processadas: " + QtdeProcessados + " de " + linhasLidas
                        + "\nLinhas zeradas: " + QtdeZeradas);
                jProgressBarImportAssoc.setValue(0);
                jButtonFiltrarEnvios.doClick();


            }
        }
    }

    public void gerarRelatorioLoja(String tipo, String data1, String data2) throws IOException, DocumentException, SQLException, ParseException {
        String TipoRelatorio = "";
        Connection con = null;
        Connection con2 = null;
        Connection con3 = null;
        Connection con4 = null;
        Connection con5 = null;
        Connection con6 = null;
        Connection con7 = null;
        Connection con8 = null;
        PdfPTable tableProduto;
        PdfPTable tableMedico;
        PdfPTable tableLojasPorLab;
        PdfPTable tableLojasPorProd;
        PdfPTable tableLojas;




        DateFormat dateFormat = new SimpleDateFormat("ddMMyyyy_HHmmss");
        Date date = new Date();


        con2 = Conexao.getConnetion();
        con3 = Conexao.getConnetion();
        con4 = Conexao.getConnetion();


        Document doc = null;
        OutputStream os = null;
        //cria o documento tamanho A4, margens de 2,54cm
        doc = new Document(PageSize.A4, 40, 40, 40, 40);

        if (tipo == "1") {
            TipoRelatorio = "Importações por loja";
        } else if (tipo == "2") {
            TipoRelatorio = "Importações por laboratório";
        } else if (tipo == "3") {
            TipoRelatorio = "Importações por Produto";
        } else if (tipo == "4") {
            TipoRelatorio = "Importações por Médico";
        }
        //cria a stream de saída
        os = new FileOutputStream(new File(".").getAbsolutePath() + "\\Relatorios\\" + TipoRelatorio + "_" + dateFormat.format(date) + ".pdf");


        //associa a stream de saída ao
        PdfWriter.getInstance(doc, os);

        //abre o documento
        doc.open();

        //adiciona o texto ao PDF
        Paragraph p;
        //fonte
        Font f = new Font(Font.FontFamily.COURIER, 16, Font.BOLD);
        Font fmenor = new Font(Font.FontFamily.COURIER, 9, Font.NORMAL);
        Font fmenorParag = new Font(Font.FontFamily.COURIER, 3, Font.NORMAL);
        Font f2 = new Font(Font.FontFamily.COURIER, 12, Font.BOLD);
        Font flinhaResumido = new Font(Font.FontFamily.COURIER, 10, Font.NORMAL);


        if (tipo == "1") {
            jProgressBarRelatorio.setMaximum(5);
            jProgressBarRelatorio.setMinimum(0);
            jProgressBarRelatorio.setValue(1);
            jProgressBarRelatorio.setStringPainted(true);
            p = new Paragraph("Relatório de Importações por Loja");
            doc.add(p);

            p = new Paragraph("________________________________________________________________________");
            doc.add(p);

            PreparedStatement pstQtdeDeLojasDetalhe = con2.prepareStatement(""
                    + "select Associados.id_associados,Associados.razão_social,COUNT(Associados.razão_social) as Prescrições "
                    + "from "
                    + "envio_item "
                    + "join Associados on envio_item.id_filial = Associados.id_associados "
                    + "where "
                    + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                    + "group by Associados.razão_social,Associados.id_associados order by Prescrições desc ");

            ResultSet rsQtdeDeLojasDetalhe = pstQtdeDeLojasDetalhe.executeQuery();
            jProgressBarRelatorio.setMaximum(5);
            jProgressBarRelatorio.setMinimum(0);
            jProgressBarRelatorio.setValue(2);
            jProgressBarRelatorio.setStringPainted(true);
            while (rsQtdeDeLojasDetalhe.next()) {

                String loja = "Nº" + rsQtdeDeLojasDetalhe.getString(1) + " " + rsQtdeDeLojasDetalhe.getString(2) + " Total PX: " + rsQtdeDeLojasDetalhe.getString(3);
                p = new Paragraph(loja, f);
                doc.add(p);

                PreparedStatement pstProdutos = con3.prepareStatement(""
                        + "select produtos.id_produto,produtos.descricao,COUNT(produtos.descricao) as qtdePX "
                        + "from "
                        + "envio_item "
                        + "join Produtos on envio_item.id_produto = produtos.id_produto "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                        + "and id_filial = '" + rsQtdeDeLojasDetalhe.getString(1) + "' "
                        + "group by produtos.descricao,produtos.id_produto order by qtdePX desc ");
                ResultSet rsProdutos = pstProdutos.executeQuery();
                jProgressBarRelatorio.setMaximum(5);
                jProgressBarRelatorio.setMinimum(0);
                jProgressBarRelatorio.setValue(3);
                jProgressBarRelatorio.setStringPainted(true);
                while (rsProdutos.next()) {
                    tableProduto = new PdfPTable(3);

                    tableProduto = new PdfPTable(new float[]{0.1f, 0.8f, 0.1f});

                    tableProduto.setWidthPercentage(100.0f);
                    tableProduto.setHorizontalAlignment(Element.ALIGN_LEFT);

                    tableProduto.addCell(new Phrase("ID", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableProduto.addCell(new Phrase("Produto", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableProduto.addCell(new Phrase("Qtde", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    //adiciona linha do produto                      
                    tableProduto.addCell(new Phrase(rsProdutos.getString(1), FontFactory.getFont(FontFactory.HELVETICA, 8, BaseColor.BLUE)));
                    tableProduto.addCell(new Phrase(rsProdutos.getString(2), FontFactory.getFont(FontFactory.HELVETICA, 8, BaseColor.BLUE)));
                    tableProduto.addCell(new Phrase(rsProdutos.getString(3), FontFactory.getFont(FontFactory.HELVETICA, 8, BaseColor.BLUE)));
                    doc.add(tableProduto);

                    //subselect para medicos
                    tableMedico = new PdfPTable(5);

                    tableMedico = new PdfPTable(new float[]{0.6f, 0.1f, 0.1f, 0.1f, 0.1f});

                    tableMedico.setWidthPercentage(100.0f);
                    tableMedico.setHorizontalAlignment(Element.ALIGN_LEFT);

                    tableMedico.addCell(new Phrase("Nome", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableMedico.addCell(new Phrase("CRM", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableMedico.addCell(new Phrase("Estado", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableMedico.addCell(new Phrase("Status", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableMedico.addCell(new Phrase("Qtde", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));

                    PreparedStatement pstMedicos = con3.prepareStatement(""
                            + "select medicos.nome,medicos.crm,medicos.estado,medicos.status,COUNT(medicos.nome) as qtdePX "
                            + "from "
                            + "envio_item "
                            + "join medicos on envio_item.id_medico = medicos.id_medico "
                            + "where "
                            + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                            + "and id_filial = '" + rsQtdeDeLojasDetalhe.getString(1) + "' and id_produto = '" + rsProdutos.getString(1) + "' "
                            + "group by medicos.nome,medicos.crm,medicos.estado,medicos.status order by qtdePX desc  ");
                    ResultSet rsMedicos = pstMedicos.executeQuery();
                    jProgressBarRelatorio.setMaximum(5);
                    jProgressBarRelatorio.setMinimum(0);
                    jProgressBarRelatorio.setValue(4);
                    jProgressBarRelatorio.setStringPainted(true);
                    while (rsMedicos.next()) {

                        tableMedico.addCell(new Phrase(rsMedicos.getString(1), FontFactory.getFont(FontFactory.HELVETICA, 8)));
                        tableMedico.addCell(new Phrase(rsMedicos.getString(2), FontFactory.getFont(FontFactory.HELVETICA, 8)));
                        tableMedico.addCell(new Phrase(rsMedicos.getString(3), FontFactory.getFont(FontFactory.HELVETICA, 8)));
                        tableMedico.addCell(new Phrase(rsMedicos.getString(4), FontFactory.getFont(FontFactory.HELVETICA, 8)));
                        tableMedico.addCell(new Phrase(rsMedicos.getString(5), FontFactory.getFont(FontFactory.HELVETICA, 8)));


                    }
                    doc.add(tableMedico);
                    p = new Paragraph("", f);


                }


            }
            //***************************  LABORATORIO ****************************************
        } else if (tipo == "2") {
            jProgressBarRelatorio.setMaximum(5);
            jProgressBarRelatorio.setMinimum(0);
            jProgressBarRelatorio.setValue(1);
            jProgressBarRelatorio.setStringPainted(true);
            p = new Paragraph("Relatório de Importações por Loja");
            doc.add(p);

            p = new Paragraph("________________________________________________________________________");
            doc.add(p);

            PreparedStatement pstQtdeDeLaboratorio = con2.prepareStatement(""
                    + "select laboratorios.id_laboratorio,laboratorios.descricao,COUNT(laboratorios.descricao) as Prescrições  "
                    + "from "
                    + "envio_item "
                    + "join produtos on envio_item.id_produto = produtos.id_produto "
                    + "join laboratorios on produtos.id_laboratorio = laboratorios.id_laboratorio "
                    + "where "
                    + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                    + "group by laboratorios.id_laboratorio,laboratorios.descricao order by Prescrições desc  ");

            ResultSet rsQtdeDeLaboratorio = pstQtdeDeLaboratorio.executeQuery();
            jProgressBarRelatorio.setMaximum(5);
            jProgressBarRelatorio.setMinimum(0);
            jProgressBarRelatorio.setValue(2);
            jProgressBarRelatorio.setStringPainted(true);
            while (rsQtdeDeLaboratorio.next()) {

                String laboratorio = rsQtdeDeLaboratorio.getString(2) + " Total PX: " + rsQtdeDeLaboratorio.getString(3);
                p = new Paragraph(laboratorio, f);
                doc.add(p);

                PreparedStatement pstLojasPorLab = con3.prepareStatement(""
                        + "select Associados.id_associados,Associados.razão_social,COUNT(Associados.razão_social) as Prescrições "
                        + "from "
                        + "envio_item "
                        + "join produtos on envio_item.id_produto = produtos.id_produto "
                        + "join laboratorios on produtos.id_laboratorio = laboratorios.id_laboratorio "
                        + "join Associados on envio_item.id_filial = Associados.id_associados "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                        + "and laboratorios.id_laboratorio = '" + rsQtdeDeLaboratorio.getString(1) + "' "
                        + "group by Associados.razão_social,Associados.id_associados order by Prescrições desc  ");
                ResultSet rsLojasPorLab = pstLojasPorLab.executeQuery();
                jProgressBarRelatorio.setMaximum(5);
                jProgressBarRelatorio.setMinimum(0);
                jProgressBarRelatorio.setValue(3);
                jProgressBarRelatorio.setStringPainted(true);
                while (rsLojasPorLab.next()) {
                    tableLojasPorLab = new PdfPTable(3);

                    tableLojasPorLab = new PdfPTable(new float[]{0.1f, 0.8f, 0.1f});

                    tableLojasPorLab.setWidthPercentage(100.0f);
                    tableLojasPorLab.setHorizontalAlignment(Element.ALIGN_LEFT);

                    tableLojasPorLab.addCell(new Phrase("Nº", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableLojasPorLab.addCell(new Phrase("Loja", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableLojasPorLab.addCell(new Phrase("Qtde", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    //adiciona linha do produto                      
                    tableLojasPorLab.addCell(new Phrase(rsLojasPorLab.getString(1), FontFactory.getFont(FontFactory.HELVETICA, 8, BaseColor.BLUE)));
                    tableLojasPorLab.addCell(new Phrase(rsLojasPorLab.getString(2), FontFactory.getFont(FontFactory.HELVETICA, 8, BaseColor.BLUE)));
                    tableLojasPorLab.addCell(new Phrase(rsLojasPorLab.getString(3), FontFactory.getFont(FontFactory.HELVETICA, 8, BaseColor.BLUE)));
                    doc.add(tableLojasPorLab);

                    //subselect para medicos
                    tableProduto = new PdfPTable(3);

                    tableProduto = new PdfPTable(new float[]{0.7f, 0.2f, 0.1f});

                    tableProduto.setWidthPercentage(100.0f);
                    tableProduto.setHorizontalAlignment(Element.ALIGN_LEFT);

                    tableProduto.addCell(new Phrase("Produto", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableProduto.addCell(new Phrase("Codigo_Barras", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableProduto.addCell(new Phrase("Qtde", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));

                    PreparedStatement pstProdutos = con3.prepareStatement(""
                            + "select produtos.descricao,produtos.codigo_barras,COUNT(produtos.descricao) as Prescrições "
                            + "from "
                            + "envio_item "
                            + "join produtos on envio_item.id_produto = produtos.id_produto "
                            + "join laboratorios on produtos.id_laboratorio = laboratorios.id_laboratorio "
                            + "join Associados on envio_item.id_filial = Associados.id_associados "
                            + "where "
                            + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                            + "and laboratorios.id_laboratorio = '" + rsQtdeDeLaboratorio.getString(1) + "' and Associados.id_associados = '" + rsLojasPorLab.getString(1) + "' "
                            + "group by produtos.descricao,produtos.codigo_barras order by Prescrições desc  ");
                    ResultSet rsProdutos = pstProdutos.executeQuery();
                    jProgressBarRelatorio.setMaximum(5);
                    jProgressBarRelatorio.setMinimum(0);
                    jProgressBarRelatorio.setValue(4);
                    jProgressBarRelatorio.setStringPainted(true);
                    while (rsProdutos.next()) {

                        tableProduto.addCell(new Phrase(rsProdutos.getString(1), FontFactory.getFont(FontFactory.HELVETICA, 8)));
                        tableProduto.addCell(new Phrase(rsProdutos.getString(2), FontFactory.getFont(FontFactory.HELVETICA, 8)));
                        tableProduto.addCell(new Phrase(rsProdutos.getString(3), FontFactory.getFont(FontFactory.HELVETICA, 8)));

                    }
                    doc.add(tableProduto);
                    p = new Paragraph("", f);


                }


            }
//******************************Produto**********************************
        } else if (tipo == "3") {
            jProgressBarRelatorio.setMaximum(5);
            jProgressBarRelatorio.setMinimum(0);
            jProgressBarRelatorio.setValue(1);
            jProgressBarRelatorio.setStringPainted(true);
            p = new Paragraph("Relatório de Importações por Produto");
            doc.add(p);

            p = new Paragraph("________________________________________________________________________");
            doc.add(p);

            PreparedStatement pstQtdeDeProduto = con2.prepareStatement(""
                    + "select produtos.id_produto,produtos.descricao,produtos.codigo_barras,laboratorios.descricao as laboratorio,COUNT(produtos.descricao) as Prescrições "
                    + "from "
                    + "envio_item "
                    + "join produtos on envio_item.id_produto = produtos.id_produto "
                    + "join laboratorios on produtos.id_laboratorio = laboratorios.id_laboratorio "
                    + "where "
                    + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                    + "group by produtos.id_produto,produtos.descricao,produtos.codigo_barras,laboratorios.descricao order by Prescrições desc  ");

            ResultSet rsQtdeDeProduto = pstQtdeDeProduto.executeQuery();
            jProgressBarRelatorio.setMaximum(5);
            jProgressBarRelatorio.setMinimum(0);
            jProgressBarRelatorio.setValue(2);
            jProgressBarRelatorio.setStringPainted(true);
            while (rsQtdeDeProduto.next()) {

                String Produto = rsQtdeDeProduto.getString(2) + " " + rsQtdeDeProduto.getString(3) + " " + rsQtdeDeProduto.getString(4) + " Total PX: " + rsQtdeDeProduto.getString(5);
                p = new Paragraph(Produto, f2);
                doc.add(p);
                p = new Paragraph(" ", f);
                doc.add(p);

                PreparedStatement pstLojasPorProd = con3.prepareStatement(""
                        + "select Associados.id_associados,Associados.razão_social,COUNT(Associados.razão_social) as Prescrições "
                        + "from "
                        + "envio_item "
                        + "join produtos on envio_item.id_produto = produtos.id_produto "
                        + "join Associados on envio_item.id_filial = Associados.id_associados "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                        + "and produtos.id_produto = '" + rsQtdeDeProduto.getString(1) + "' "
                        + "group by Associados.razão_social,Associados.id_associados order by Prescrições desc ");
                ResultSet rsLojasPorProd = pstLojasPorProd.executeQuery();
                jProgressBarRelatorio.setMaximum(5);
                jProgressBarRelatorio.setMinimum(0);
                jProgressBarRelatorio.setValue(3);
                jProgressBarRelatorio.setStringPainted(true);
                while (rsLojasPorProd.next()) {
                    tableLojasPorProd = new PdfPTable(3);

                    tableLojasPorProd = new PdfPTable(new float[]{0.1f, 0.8f, 0.1f});

                    tableLojasPorProd.setWidthPercentage(100.0f);
                    tableLojasPorProd.setHorizontalAlignment(Element.ALIGN_LEFT);

                    tableLojasPorProd.addCell(new Phrase("Nº", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableLojasPorProd.addCell(new Phrase("Loja", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableLojasPorProd.addCell(new Phrase("Qtde", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));

                    tableLojasPorProd.addCell(new Phrase(rsLojasPorProd.getString(1), FontFactory.getFont(FontFactory.HELVETICA, 8, BaseColor.BLUE)));
                    tableLojasPorProd.addCell(new Phrase(rsLojasPorProd.getString(2), FontFactory.getFont(FontFactory.HELVETICA, 8, BaseColor.BLUE)));
                    tableLojasPorProd.addCell(new Phrase(rsLojasPorProd.getString(3), FontFactory.getFont(FontFactory.HELVETICA, 8, BaseColor.BLUE)));
                    doc.add(tableLojasPorProd);

                    //subselect para medicos
                    tableMedico = new PdfPTable(5);

                    tableMedico = new PdfPTable(new float[]{0.6f, 0.1f, 0.1f, 0.1f, 0.1f});

                    tableMedico.setWidthPercentage(100.0f);
                    tableMedico.setHorizontalAlignment(Element.ALIGN_LEFT);

                    tableMedico.addCell(new Phrase("Nome", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableMedico.addCell(new Phrase("CRM", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableMedico.addCell(new Phrase("Estado", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableMedico.addCell(new Phrase("Status", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableMedico.addCell(new Phrase("Qtde", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));


                    PreparedStatement pstMedico = con3.prepareStatement(""
                            + "select medicos.nome,medicos.crm,medicos.estado,medicos.status,COUNT(medicos.nome) as qtdePX "
                            + "from "
                            + "envio_item "
                            + "join medicos on envio_item.id_medico = medicos.id_medico "
                            + "where "
                            + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                            + "and id_filial = '" + rsLojasPorProd.getString(1) + "' and id_produto = '" + rsQtdeDeProduto.getString(1) + "' "
                            + "group by medicos.nome,medicos.crm,medicos.estado,medicos.status order by qtdePX desc  ");
                    ResultSet rsMedico = pstMedico.executeQuery();
                    jProgressBarRelatorio.setMaximum(5);
                    jProgressBarRelatorio.setMinimum(0);
                    jProgressBarRelatorio.setValue(4);
                    jProgressBarRelatorio.setStringPainted(true);
                    while (rsMedico.next()) {

                        tableMedico.addCell(new Phrase(rsMedico.getString(1), FontFactory.getFont(FontFactory.HELVETICA, 8)));
                        tableMedico.addCell(new Phrase(rsMedico.getString(2), FontFactory.getFont(FontFactory.HELVETICA, 8)));
                        tableMedico.addCell(new Phrase(rsMedico.getString(3), FontFactory.getFont(FontFactory.HELVETICA, 8)));
                        tableMedico.addCell(new Phrase(rsMedico.getString(4), FontFactory.getFont(FontFactory.HELVETICA, 8)));
                        tableMedico.addCell(new Phrase(rsMedico.getString(5), FontFactory.getFont(FontFactory.HELVETICA, 8)));

                    }
                    doc.add(tableMedico);
                    p = new Paragraph("", f);


                }


            }


            //******************************MEDICO**********************************
        } else if (tipo == "4") {
            jProgressBarRelatorio.setMaximum(5);
            jProgressBarRelatorio.setMinimum(0);
            jProgressBarRelatorio.setValue(1);
            jProgressBarRelatorio.setStringPainted(true);
            p = new Paragraph("Relatório de Importações por Médico");
            doc.add(p);

            p = new Paragraph("________________________________________________________________________");
            doc.add(p);

            PreparedStatement pstQtdeDeMedico = con2.prepareStatement(""
                    + "select medicos.id_medico,medicos.nome,medicos.crm,medicos.estado,medicos.status,COUNT(medicos.nome) as qtdePX "
                    + "from "
                    + "envio_item "
                    + "join medicos on envio_item.id_medico = medicos.id_medico "
                    + "where "
                    + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                    + "group by medicos.id_medico,medicos.nome,medicos.crm,medicos.estado,medicos.status order by qtdePX desc  ");

            ResultSet rsQtdeDeMedico = pstQtdeDeMedico.executeQuery();
            jProgressBarRelatorio.setMaximum(5);
            jProgressBarRelatorio.setMinimum(0);
            jProgressBarRelatorio.setValue(2);
            jProgressBarRelatorio.setStringPainted(true);
            while (rsQtdeDeMedico.next()) {

                String Medico = rsQtdeDeMedico.getString(2) + " " + rsQtdeDeMedico.getString(3) + " " + rsQtdeDeMedico.getString(4) + " "
                        + rsQtdeDeMedico.getString(5) + " Total PX: " + rsQtdeDeMedico.getString(6);
                p = new Paragraph(Medico, f2);
                doc.add(p);
                p = new Paragraph(" ", f);
                doc.add(p);

                PreparedStatement pstLojasPorMed = con3.prepareStatement(""
                        + "select Associados.id_associados,Associados.razão_social,COUNT(Associados.razão_social) as Prescrições "
                        + "from "
                        + "envio_item "
                        + "join medicos on envio_item.id_medico = medicos.id_medico "
                        + "join Associados on envio_item.id_filial = Associados.id_associados "
                        + "where "
                        + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                        + "and medicos.id_medico = '" + rsQtdeDeMedico.getString(1) + "' "
                        + "group by Associados.razão_social,Associados.id_associados order by Prescrições desc");
                ResultSet rsLojasPorMed = pstLojasPorMed.executeQuery();
                jProgressBarRelatorio.setMaximum(5);
                jProgressBarRelatorio.setMinimum(0);
                jProgressBarRelatorio.setValue(3);
                jProgressBarRelatorio.setStringPainted(true);
                while (rsLojasPorMed.next()) {
                    tableLojas = new PdfPTable(3);

                    tableLojas = new PdfPTable(new float[]{0.1f, 0.8f, 0.1f});

                    tableLojas.setWidthPercentage(100.0f);
                    tableLojas.setHorizontalAlignment(Element.ALIGN_LEFT);

                    tableLojas.addCell(new Phrase("Nº", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableLojas.addCell(new Phrase("Loja", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableLojas.addCell(new Phrase("Qtde", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));

                    tableLojas.addCell(new Phrase(rsLojasPorMed.getString(1), FontFactory.getFont(FontFactory.HELVETICA, 8, BaseColor.BLUE)));
                    tableLojas.addCell(new Phrase(rsLojasPorMed.getString(2), FontFactory.getFont(FontFactory.HELVETICA, 8, BaseColor.BLUE)));
                    tableLojas.addCell(new Phrase(rsLojasPorMed.getString(3), FontFactory.getFont(FontFactory.HELVETICA, 8, BaseColor.BLUE)));
                    doc.add(tableLojas);

                    //subselect para produto
                    tableProduto = new PdfPTable(4);

                    tableProduto = new PdfPTable(new float[]{0.5f, 0.2f, 0.2f, 0.1f});

                    tableProduto.setWidthPercentage(100.0f);
                    tableProduto.setHorizontalAlignment(Element.ALIGN_LEFT);

                    tableProduto.addCell(new Phrase("Produto", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableProduto.addCell(new Phrase("Codigo Barras", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableProduto.addCell(new Phrase("Laboratório", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));
                    tableProduto.addCell(new Phrase("Qtde", FontFactory.getFont(FontFactory.HELVETICA_BOLD, 8)));


                    PreparedStatement pstProduto = con3.prepareStatement(""
                            + "select produtos.id_produto,produtos.descricao,produtos.codigo_barras,laboratorios.descricao as laboratorio,COUNT(produtos.descricao) as Prescrições "
                            + "from "
                            + "envio_item "
                            + "join medicos on envio_item.id_medico = medicos.id_medico "
                            + "join produtos on envio_item.id_produto = produtos.id_produto "
                            + "join laboratorios on produtos.id_laboratorio = laboratorios.id_laboratorio "
                            + "where "
                            + "envio_item.data >= '" + data1 + "' and envio_item.data <= '" + data2 + "' "
                            + "and medicos.id_medico = '" + rsQtdeDeMedico.getString(1) + "' and envio_item.id_filial = '" + rsLojasPorMed.getString(1) + "' "
                            + "group by produtos.id_produto,produtos.descricao,produtos.codigo_barras,laboratorios.descricao order by Prescrições desc ");
                    ResultSet rsProduto = pstProduto.executeQuery();
                    jProgressBarRelatorio.setMaximum(5);
                    jProgressBarRelatorio.setMinimum(0);
                    jProgressBarRelatorio.setValue(4);
                    jProgressBarRelatorio.setStringPainted(true);
                    while (rsProduto.next()) {

                        tableProduto.addCell(new Phrase(rsProduto.getString(2), FontFactory.getFont(FontFactory.HELVETICA, 8)));
                        tableProduto.addCell(new Phrase(rsProduto.getString(3), FontFactory.getFont(FontFactory.HELVETICA, 8)));
                        tableProduto.addCell(new Phrase(rsProduto.getString(4), FontFactory.getFont(FontFactory.HELVETICA, 8)));
                        tableProduto.addCell(new Phrase(rsProduto.getString(5), FontFactory.getFont(FontFactory.HELVETICA, 8)));

                    }
                    doc.add(tableProduto);
                    p = new Paragraph("", f);


                }


            }

        }
        jProgressBarRelatorio.setMaximum(5);
        jProgressBarRelatorio.setMinimum(0);
        jProgressBarRelatorio.setValue(5);
        jProgressBarRelatorio.setStringPainted(true);
        doc.close();
    }

    private void formWindowOpened(java.awt.event.WindowEvent evt) {
        new Thread("Rotinas Iniciais") {
            public void run() {

                importaAssociados();

            }
        }.start();
    }

    private void formWindowOpenedProcRet(java.awt.event.WindowEvent evt) {
        new Thread("Rotinas Iniciais") {
            public void run() {
                try {
                    processarRetorno();
                } catch (SQLException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                }

            }
        }.start();
    }

    private void formWindowOpened2(java.awt.event.WindowEvent evt) {
        new Thread("Rotinas Iniciais") {
            public void run() {
                try {
                    importaMedico();
                } catch (IOException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                } catch (SQLException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                }

            }
        }.start();
    }

    private void formWindowOpened3(java.awt.event.WindowEvent evt) {
        new Thread("Rotinas Iniciais") {
            public void run() {
                try {

                    importaEnvio();
                } catch (IOException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                } catch (SQLException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                }

            }
        }.start();
    }

    private void formWindowOpenedPM(java.awt.event.WindowEvent evt) {
        new Thread("Rotinas Iniciais") {
            public void run() {

                preencheGridMedicos();


            }
        }.start();
    }

    private void formWindowOpenedInformacoes(java.awt.event.WindowEvent evt) {
        new Thread("Rotinas Iniciais") {
            public void run() {

                preencheGridImportacoesResumo();


            }
        }.start();
    }

    private void formWindowOpenedRelatorio(java.awt.event.WindowEvent evt) {
        new Thread("Rotinas Iniciais") {
            public void run() {

                try {

                    SimpleDateFormat formato = new SimpleDateFormat("yyyy-MM-dd");
                    String data1 = formato.format(jXDatePickerRELini.getDate());
                    String data2 = formato.format(jXDatePickerRELfim.getDate());


                    if (jComboBoxTipoRelatorio.getSelectedIndex() == 0) { //loja

                        gerarRelatorioLoja("1", data1, data2);

                        JOptionPane.showMessageDialog(null, "Relatório gerado com sucesso!");
                    } else if (jComboBoxTipoRelatorio.getSelectedIndex() == 1) { //laboratorio

                        gerarRelatorioLoja("2", data1, data2);

                        JOptionPane.showMessageDialog(null, "Relatório gerado com sucesso!");
                    } else if (jComboBoxTipoRelatorio.getSelectedIndex() == 2) { //Produto

                        gerarRelatorioLoja("3", data1, data2);

                        JOptionPane.showMessageDialog(null, "Relatório gerado com sucesso!");
                    } else if (jComboBoxTipoRelatorio.getSelectedIndex() == 3) { //Medico

                        gerarRelatorioLoja("4", data1, data2);

                        JOptionPane.showMessageDialog(null, "Relatório gerado com sucesso!");
                    }
                } catch (IOException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                } catch (DocumentException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                } catch (SQLException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                } catch (ParseException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                }


            }
        }.start();
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /* Set the Nimbus look and feel */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
         */
        try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }


            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(PrincipalCloseUp.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(PrincipalCloseUp.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(PrincipalCloseUp.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(PrincipalCloseUp.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>

        /* Create and display the form */
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {


                Properties props = new Properties();

                props.put("logoString", " ");

                FastLookAndFeel.setCurrentTheme(props);
                try {

                    //     UIManager.setLookAndFeel("com.jtattoo.plaf.mint.MintLookAndFeel");
                    //     UIManager.setLookAndFeel("com.jtattoo.plaf.smart.SmartLookAndFeel");
                    //UIManager.setLookAndFeel("com.jtattoo.plaf.acryl.AcrylLookAndFeel");
                    UIManager.setLookAndFeel("com.jtattoo.plaf.fast.FastLookAndFeel");
                    /*
                     * 
                     * UIManager.setLookAndFeel("com.jtattoo.plaf.acryl.AcrylLookAndFeel"); //NON-NLS NON-NLS
                     UIManager.setLookAndFeel("com.jtattoo.plaf.aero.AeroLookAndFeel"); //NON-NLS NON-NLS
                     UIManager.setLookAndFeel("com.jtattoo.plaf.aluminium.AluminiumLookAndFeel"); //NON-NLS NON-NLS
                     UIManager.setLookAndFeel("com.jtattoo.plaf.bernstein.BernsteinLookAndFeel"); //NON-NLS NON-NLS
                     UIManager.setLookAndFeel("com.jtattoo.plaf.fast.FastLookAndFeel"); //NON-NLS NON-NLS
                     UIManager.setLookAndFeel("com.jtattoo.plaf.graphite.GraphiteLookAndFeel"); //NON-NLS NON-NLS
                     UIManager.setLookAndFeel("com.jtattoo.plaf.hifi.HiFiLookAndFeel"); //NON-NLS NON-NLS
                     UIManager.setLookAndFeel("com.jtattoo.plaf.luna.LunaLookAndFeel"); //NON-NLS NON-NLS
                     UIManager.setLookAndFeel("com.jtattoo.plaf.mcwin.McWinLookAndFeel"); //NON-NLS NON-NLS
                     UIManager.setLookAndFeel("com.jtattoo.plaf.mint.MintLookAndFeel"); //NON-NLS NON-NLS
                     UIManager.setLookAndFeel("com.jtattoo.plaf.noire.NoireLookAndFeel"); //NON-NLS NON-NLS
                     UIManager.setLookAndFeel("com.jtattoo.plaf.smart.SmartLookAndFeel"); //NON-NLS NON-NLS
                     */



                } catch (ClassNotFoundException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                } catch (InstantiationException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                } catch (IllegalAccessException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                } catch (UnsupportedLookAndFeelException ex) {
                    Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                }

                PrincipalCloseUp p = new PrincipalCloseUp();
                p.setLocationRelativeTo(null);

                Connection conTranca = Conexao.getConnetion();
                //   if (getImp2().equals("BFEBFBFF000306A9")) { // NOTEBOOK
                if (getImp2().equals("BFEBFBFF000206D7")) { //PC
                    //            if (getImp2().equals("AFEBFBFF00010661")) {//luis note
                    try {
                        PreparedStatement pstTranca = conTranca.prepareStatement("select * from parame");
                        ResultSet rsTranca = pstTranca.executeQuery();

                        while (rsTranca.next()) {
                            Date data = new Date(System.currentTimeMillis());
                            SimpleDateFormat formatador = new SimpleDateFormat("yyyyMMdd");
                            String dataFormatada = formatador.format(data);
                            Cript cpt = new Cript();
                            String dtNowCript = cpt.SetCript(dataFormatada);

                            String dtBdCript = rsTranca.getString("imp1");
                            String dtBdDecript = cpt.GetDecript(dtBdCript);

                            if (dtBdDecript.length() == 8) {
                                if (Integer.parseInt(dtBdDecript) >= Integer.parseInt(dataFormatada)) {
                                    String a = cpt.GetDecript(rsTranca.getString("imp2"));
                                    if (Integer.parseInt(dataFormatada) >= Integer.parseInt(a)) {

                                        if (cpt.GetDecript(rsTranca.getString("imp2")).length() == 8) {
                                            Connection conG = Conexao.getConnetion();
                                            PreparedStatement pstG = conG.prepareStatement("update parame set imp2 = '" + dtNowCript + "'");
                                            pstG.executeUpdate();

                                            p.setVisible(true);
                                        } else {
                                            JOptionPane.showMessageDialog(null, "Erro na abertura do sistema. Informe o codigo 8775");

                                        }
                                    } else {
                                        JOptionPane.showMessageDialog(null, "Erro na abertura do sistema. Informe o codigo 8777");
                                    }
                                } else {
                                    JOptionPane.showMessageDialog(null, "Erro na abertura do sistema. Informe o codigo 8708");
                                }
                            } else {
                                JOptionPane.showMessageDialog(null, "Erro na abertura do sistema. Informe o codigo 8708");
                            }
                        }
                    } catch (SQLException ex) {
                        Logger.getLogger(PrincipalCloseUp.class.getName()).log(Level.SEVERE, null, ex);
                    }

                } else {
                    JOptionPane.showMessageDialog(null, "Erro na abertura do sistema. Informe o codigo 8778");
                }
            }
        });

    }

    public static String getImp2() {
        String result = "";
        try {
            File file = File.createTempFile("tmp", ".vbs");
            file.deleteOnExit();
            FileWriter fw = new java.io.FileWriter(file);

            String vbs =
                    "On Error Resume Next \r\n\r\n"
                    + "strComputer = \".\"  \r\n"
                    + "Set objWMIService = GetObject(\"winmgmts:\" _ \r\n"
                    + "    & \"{impersonationLevel=impersonate}!\\\\\" & strComputer & \"\\root\\cimv2\") \r\n"
                    + "Set colItems = objWMIService.ExecQuery(\"Select * from Win32_Processor\")  \r\n "
                    + "For Each objItem in colItems\r\n "
                    + "    Wscript.Echo objItem.ProcessorId  \r\n "
                    + "    exit for  ' do the first cpu only! \r\n"
                    + "Next                    ";


            fw.write(vbs);
            fw.close();
            Process p = Runtime.getRuntime().exec("cscript //NoLogo " + file.getPath());
            BufferedReader input =
                    new BufferedReader(new InputStreamReader(p.getInputStream()));
            String line;
            while ((line = input.readLine()) != null) {
                result += line;
            }
            input.close();
        } catch (Exception e) {
        }
        if (result.trim().length() < 1 || result == null) {
            result = "NO_CPU_ID";
        }
        return result.trim();
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JLabel JLabelTotal;
    private javax.swing.JButton jButton1;
    private javax.swing.JButton jButton10;
    private javax.swing.JButton jButton11;
    private javax.swing.JButton jButton12;
    private javax.swing.JButton jButton13;
    private javax.swing.JButton jButton14;
    private javax.swing.JButton jButton16;
    private javax.swing.JButton jButton2;
    private javax.swing.JButton jButton3;
    private javax.swing.JButton jButton4;
    private javax.swing.JButton jButton5;
    private javax.swing.JButton jButton6;
    private javax.swing.JButton jButton7;
    private javax.swing.JButton jButton8;
    private javax.swing.JButton jButton9;
    private javax.swing.JButton jButtonBuscar;
    private javax.swing.JButton jButtonFiltrarEnvios;
    private javax.swing.JComboBox jComboBoxEstado;
    private javax.swing.JComboBox jComboBoxTipoRelatorio;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel10;
    private javax.swing.JLabel jLabel11;
    private javax.swing.JLabel jLabel12;
    private javax.swing.JLabel jLabel13;
    private javax.swing.JLabel jLabel14;
    private javax.swing.JLabel jLabel15;
    private javax.swing.JLabel jLabel16;
    private javax.swing.JLabel jLabel17;
    private javax.swing.JLabel jLabel18;
    private javax.swing.JLabel jLabel19;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JLabel jLabel7;
    private javax.swing.JLabel jLabel8;
    private javax.swing.JLabel jLabel9;
    private javax.swing.JLabel jLabelLidas;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel10;
    private javax.swing.JPanel jPanel11;
    private javax.swing.JPanel jPanel12;
    private javax.swing.JPanel jPanel13;
    private javax.swing.JPanel jPanel14;
    private javax.swing.JPanel jPanel15;
    private javax.swing.JPanel jPanel16;
    private javax.swing.JPanel jPanel17;
    private javax.swing.JPanel jPanel18;
    private javax.swing.JPanel jPanel19;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JPanel jPanel20;
    private javax.swing.JPanel jPanel3;
    private javax.swing.JPanel jPanel4;
    private javax.swing.JPanel jPanel5;
    private javax.swing.JPanel jPanel6;
    private javax.swing.JPanel jPanel7;
    private javax.swing.JPanel jPanel8;
    private javax.swing.JPanel jPanel9;
    private javax.swing.JProgressBar jProgressBarImportAssoc;
    private javax.swing.JProgressBar jProgressBarInfo;
    private javax.swing.JProgressBar jProgressBarLinhasEnvio;
    private javax.swing.JProgressBar jProgressBarProcRetorno;
    private javax.swing.JProgressBar jProgressBarQtdeItemEnvio;
    private javax.swing.JProgressBar jProgressBarQtdeLinhas;
    private javax.swing.JProgressBar jProgressBarQtdeSelecionado;
    private javax.swing.JProgressBar jProgressBarRelatorio;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane10;
    private javax.swing.JScrollPane jScrollPane11;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JScrollPane jScrollPane3;
    private javax.swing.JScrollPane jScrollPane4;
    private javax.swing.JScrollPane jScrollPane5;
    private javax.swing.JScrollPane jScrollPane6;
    private javax.swing.JScrollPane jScrollPane7;
    private javax.swing.JScrollPane jScrollPane8;
    private javax.swing.JScrollPane jScrollPane9;
    private javax.swing.JTabbedPane jTabbedPane1;
    private javax.swing.JTabbedPane jTabbedPane10;
    private javax.swing.JTabbedPane jTabbedPane11;
    private javax.swing.JTabbedPane jTabbedPane2;
    private javax.swing.JTabbedPane jTabbedPane3;
    private javax.swing.JTabbedPane jTabbedPane5;
    private javax.swing.JTabbedPane jTabbedPane6;
    private javax.swing.JTabbedPane jTabbedPane7;
    private javax.swing.JTabbedPane jTabbedPane8;
    private javax.swing.JTabbedPane jTabbedPane9;
    private javax.swing.JTable jTableAssociados;
    private javax.swing.JTable jTableDLaboratorio;
    private javax.swing.JTable jTableDLojasDentro;
    private javax.swing.JTable jTableDLojasFora;
    private javax.swing.JTable jTableDMedicos;
    private javax.swing.JTable jTableDProduto;
    private javax.swing.JTable jTableInformacoesResumidas;
    private javax.swing.JTable jTableMedicos;
    private javax.swing.JTextArea jTextArea2;
    private javax.swing.JTextArea jTextAreaEnvioCloseUp;
    private javax.swing.JTextArea jTextAreaMedicos;
    private javax.swing.JTextField jTextFieldAssociadoViaArq;
    private javax.swing.JTextField jTextFieldBusca;
    private javax.swing.JTextField jTextFieldBuscaMedico;
    private javax.swing.JTextField jTextFieldCaminhoEnvioCloseUp;
    private javax.swing.JTextField jTextFieldCaminhoMedico;
    private javax.swing.JTextField jTextFieldCaminhoPlanilha;
    private javax.swing.JTextField jTextFieldCaminhoRetCloseUp;
    private javax.swing.JTextField jTextFieldCaminhoRetornoCloseUp;
    private javax.swing.JTextField jTextFieldEnvioCloseUp;
    private javax.swing.JTextField jTextFieldMedico;
    private org.jdesktop.swingx.JXDatePicker jXDatePickerFim;
    private org.jdesktop.swingx.JXDatePicker jXDatePickerIni;
    private org.jdesktop.swingx.JXDatePicker jXDatePickerRELfim;
    private org.jdesktop.swingx.JXDatePicker jXDatePickerRELini;
    // End of variables declaration//GEN-END:variables
}
